Instead of using OFFSET to create a dynamic range, you can use the non-volatile INDEX function.
Thanks to Roger Govier, who created this tutorial and sample file
Instead of using OFFSET to create a dynamic range, you can use the non-volatile INDEX function. Then, use the dynamic range names to
In the sample file there's a macro that will create the range names automatically for you.
You can download and use the sample file, or create your own file to use, as described here.
Because we are going to need all ranges to be of equal length, and to save having to use the COUNTA function over and over, it is easier to create a dynamic range for the number of rows, which we will give the shortened name lrow
Next, you'll create a dynamic range for the YearMth column.
You could follow the same steps to create dynamic ranges for the remaining columns in the table, changing the column reference in the Refers To formula. We'll manually create one more dynamic range -- for the Region, which is in column B.
You could repeat the steps for all the remaining columns, but that would be a long, repetitive process. To make things easier, and much faster, you can use a macro to automate the naming.
This only requires a small amount of VBA code, as shown below. This code is also in the sample file that you can download.
In addition to lrow, the VBA code creates a name lcol to hold the last Column number used on the sheet. It also adds a Dynamic Range called MyData which covers the whole data set and is then used when producing a Pivot Table.
Sub CreateNames() 'https://www.contextures.com/xlNames03.html ' written by Roger Govier, Technology4U Dim wb As Workbook, ws As Worksheet Dim lrow As Long, lcol As Long, i As Long Dim myName As String, Start As String ' set the row number where headings are held as a constant ' change this to the row number required if not row 1 Const Rowno = 1 ' set the Offset as the number of rows below Rowno, where the ' data begins Const Offset = 1 ' set the starting column for the data, in this case 1 ' change if the data does not start in column A Const Colno = 1 On Error GoTo CreateNames_Error Set wb = ActiveWorkbook Set ws = ActiveSheet ' count the number of columns used in the row designated to ' have the header names lcol = ws.Cells(Rowno, 1).End(xlToRight).Column lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row Start = Cells(Rowno, Colno).Address wb.Names.Add Name:="lcol", _ RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")" wb.Names.Add Name:="lrow", _ RefersToR1C1:="=COUNTA(C" & Colno & ")" wb.Names.Add Name:="myData", RefersTo:= _ "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)" For i = Colno To lcol ' if a column header contains spaces, ' replace the space with an underscore ' spaces are not allowed in range names. myName = Replace(Cells(Rowno, i).Value, " ", "_") If myName = "" Then ' if column header is blank, warn the user and ' stop the macro at that point ' names will only be created for those cells with text in them. MsgBox "Missing Name in column " & i & vbCrLf _ & "Please Enter a Name and run macro again" Exit Sub End If wb.Names.Add Name:=myName, RefersToR1C1:= _ "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)" nexti: Next i On Error GoTo 0 MsgBox "All dynamic Named ranges have been created" Exit Sub CreateNames_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure CreateNames of Module Technology4U" End Sub
Examples are shown in the sample file of how to use Sumproduct formulas with Range Names.
Examples are shown in the sample file of how to create pivot tables using Range Names.
For example, the macro creates a Dynamic Range called myData, with a formula of :
Then setting up a Pivot table report is very easy -- just use myData as the range in Step 2 of the PivotTable and PivotChart Wizard.
And all the data is summarized, with no complex formulas required. Also, you can quickly rearrange the pivot table, by dragging the fields to a different location. For example, you could drag Item to the Row area, beside the Region field, for a different layout.
Download the zipped CreateNames sample file
Last updated: May 1, 2021 10:18 AM