Posts

Using INDEX MATCH MATCH

-
Image
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
Post a Comment
Read more

INDEX MATCH with multiple criteria

-
Image
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-
Post a Comment
Read more

Formatting a Range of Cells In Excel VBA

-
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
Post a Comment
Read more

UPDATE THE TIME OF VALUE CHANGED IN A COLUMN

-
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
Post a Comment
Read more

How to create new sheet and name through a cell range

-
Image
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
Post a Comment
Read more

How to Insert Rows in Excel Worksheet using VBA – Solution(s)

-
Image
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
Post a Comment
Read more

BREAK THE PASSWORD OF THE SHEET IN EXCEL

-
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
Post a Comment
Read more