Being.Shadaan (ExcelWorld)

Using INDEX MATCH MATCH If you use  INDEX MATCH  frequently in your worksheets, you may be surprised to learn about an even more powerful version of the formula:  INDEX MATCH MATCH . This variant allows you to look up values across rows and columns simultaneously. Let's take a look at how this new construction works.  Proficiency with the basic  INDEX MATCH  formulation is a prerequisite for this tutorial. If you don't already have a solid grasp of it, check out our basic  INDEX MATCH tutorial first. Defining the problem The following spreadsheet lists two metrics —  Cookie packs sold  and  Revenue  — for SnackWorld by month from January through May. We know how to run an  INDEX MATCH  on either one of these columns to pull a specific metric by month, using the standard formula: = INDEX ( range , MATCH ( lookup_value , lookup_range , match_type )) But, what if we also want to add a flexible input for the column as well as the row? Say, for example, we want to te

INDEX MATCH with multiple criteria So, you're an  INDEX MATCH  expert, using it to replace  VLOOKUP  entirely. But there are still a few lookups that you're not sure how to perform. Most importantly, you'd like to be able to look up a value based on multiple criteria within separate columns. Fortunately, there is a solution. We can combine  INDEX MATCH  with a new tool called "array formulas" to look up a value based on multiple criteria. Here's how.  Familiarity with  INDEX MATCH  is a pre-requisite for this tutorial. If you're not yet comfortable with it, check out our INDEX MATCH tutorial to get up to speed. Defining the problem The spreadsheet below lists SnackWorld sales of both Cookies and Brownies by month. The spreadsheet is in what we call flat-file format, meaning that each separate combination of item category-month is on its own row. We want to be able to look up the number of units sold based on a particular combination of item-

Formatting Cells Number General Range("A1").NumberFormat = "General" Number Range("A1").NumberFormat = "0.00" Currency Range("A1").NumberFormat = "$#,##0.00" Accounting Range("A1").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" Date Range("A1").NumberFormat = "yyyy-mm-dd;@" Time Range("A1").NumberFormat = "h:mm:ss AM/PM;@" Percentage Range("A1").NumberFormat = "0.00%" Fraction Range("A1").NumberFormat = "# ?/?" Scientific Range("A1").NumberFormat = "0.00E+00" Text Range("A1").NumberFormat = "@" Special Range("A1").NumberFormat = "00000" Custom Range("A1").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" Formatting Cells Alignment Text Alignment Horizontal The value o

UPDATE THE TIME OF VALUE CHANGED IN A COLUMN  Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("D:D")) Is Nothing Then Target.Offset(0, 1).Value = Application.WorksheetFunction.Text(Now, "dd/mm/yyyy hh:mm AM/PM") End If End Sub

How to create new sheet  and name through a cell range go to developer tab and create a macro using below code Sub CreateSheets() Sheets(" Sheet 1 ").Select Words = Cells.Range (" A2:A46 ") For Each Item In Words Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Item Next Item End Sub

How to Insert Rows in Excel Worksheet using VBA – Solution(s): We can insert use EntireRow.Insert method to insert rows. The following is the example code to inserting rows in excel worksheet. VBA insert rows excel – An Example The following example will show you how to insert a row in Excel Worksheet. You can insert multiple rows at a time. Code: Sub sbInsertingRows() 'Inserting a Row at at Row 2 Range("A2").EntireRow.Insert ' 'Inserting 3 Rows from 3 Rows("3:5").EntireRow.Insert End Sub Instructions: Open an excel workbook Press Alt+F11 to open VBA Editor Insert a Module for Insert Menu Copy the above code and Paste in the code window Save the file as macro enabled workbook Press F5 to run it Input: Shcreen-shot of example, before executing of the above code. You can see the 10 rows of data available in the worksheet. Output: Shcreen-shot of example, after executing of the above code. You can see the 4 new rows are inserted

Below steps will guide you to hack or unlock the Password of the excel Sheet Press ALT + F11 or click on View Code in Developers Tabs In the visual basic Enter the below Code. Do not change the code just copy paste: Sub PasswordBreaker() 'Breaks worksheet password protection. Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If ActiveSheet.ProtectContents = False Then MsgBox "One usable password is " & Chr(i) & C

Use below code to in EXCEL by creating a VBA Code VBA CODE 1 Private Sub CmdSaveThisWorkBook_Click() Application.DisplayAlerts = False         ThisWorkbook.SaveAs Filename:=Range("C4").Value & ":\" & Range("C3").Value & "(" & Format(Now(), "DD-MMM-YYYY hh mm AMPM") & ")" & ".xls", FileFormat:=56 Application.DisplayAlerts = True End Sub VBA CODE 2 Option Explicit Sub saveWork() Dim name As String Worksheets("Sheet1").Select Range("A1").Select  'change range here name = Range("A1").Value  'change range here ActiveWorkbook.SaveAs Filename:="E:\" & name, FileFormat:=50 'change path here '            Format "xls": FileFormatValue = 56 '            Format "xlsx": FileFormatValue = 51 '            Format "xlsm": FileFormatValue = 52 '            Format "xlsb": Fi

Exact() Function The Excel Exact function tests if two supplied text strings or values are exactly equal and if so, returns TRUE; Otherwise, the function returns FALSE. The function is case-sensitive. Syntax The syntax for the EXACT function in Microsoft Excel is: EXACT( text1, text2 ) Parameters or Arguments text1 and text2 The two string values to compare. Exact Function Examples In the following spreadsheet, the Excel Exact function is used to compare four different pairs of text strings. Formulas: A B C 1 Text Text =EXACT( A1, B1 ) 2 Text text =EXACT( A2, B2 ) 3 5321.222 5321.222 =EXACT( A3, B3 ) 4 0.5 12:00 =EXACT( A4, B4 ) Results: A B C 1 Text Text TRUE 2 Text text FALSE 3 5321.222 5321.222 TRUE 4 0.5 12:00 TRUE Note that, in the above examples: -            As the Excel Exact function is case-sensitive, the functio

Excel Error Messages If Excel detects that your Excel Formula or Function contains an error, it will return an error message (e.g. #VALUE!, #N/A). The error message that you are presented with, provides information about the type and cause of the Excel formula error. It can therefore assist you with identifying and fixing the problem. The notes below provide a quick reference guide of what each of the different error messages means. ##### When you see ##### displayed in your cell, it can look a little scary. The good news is that this simply means the column isn't wide enough to display the value you've inputted. And that's any easy fix! How to Resolve This Error:   Click on the right border of the column header and increase the column width. Also you can double-click the right border of the header to automatically fit the widest cell in that column. #DIV/0 Excel displays the #DIV/0! error when a formula tries to divide a nu