This is the fourth and final in a series of posts in which I will introduce you to the Excel concept of dynamic named ranges, which are a very useful piece of knowledge to have at your disposal. In this weeks post, some of the more advanced aspects of dynamic named ranges.

Advanced Material

Match

When defining a dynamically named range, one method you can use is the Offset – CountA example that I talked about earlier. This is great, BUT if there are blank cells in the column or row, there are problems. An alternative to the “Offset – CountA” method is the “Match” method. Simply replace the CountA portion of the formula with:

MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9E+307,A:A,1),0))

This rather complicated formula will provide the row number of the last non blank cell in the column!

Single Column:

Having used a dynamically named range to define the entire sales table, you may find that you need a particular column (or row) of the table for various reasons. This CAN also be defined by name, and can be linked to the previous name. A simple index function can return a particularly numbered column (or row) from a table. For example:

=INDEX(SalesTable,0,7)

returns the 7th column of the table. However, if you “move things around”, the 7th may no longer be relevant. My rule of thumb is that “1st” is acceptable, because you almost NEVER move the first column of a table, but anything else should be referenced with a Match. For example if we want a reference to the “Total” column of sales table, we can use the Match function to find the column with “Total” as the heading. Like so:

=INDEX(SalesTable,0,MATCH(“Total”,OFFSET(INDEX(SalesTable,1,0),-1,0),0))

Remembering that we have not been including the headings in our named range, we reference it by getting the first row:

INDEX(SalesTable,1,0)

and offsetting this by -1 rows (ie up 1 row). Of course if we are going to be using the headings frequently, we could always define them as their own named range:

=OFFSET(INDEX(SalesTable,1,0),-1,0)

called “SalesTable_Heading” and the “Total” named range would look like:

=INDEX(SalesTable,0,MATCH(“Total”,SalesTable_Heading,0))

Relative Named Ranges

By default all named ranges are absolute, and generally this is a GOOD thing. However you CAN make relative named ranges. There are VERY few times this is worthwhile, but for the sake of completeness here are some examples that no one has ever actually used. EVER.

Right

If you select cell A1 and then use the Name Manager to define a named range called “right”. Define this as B2 (ensuring that you have no $ signs), and the formula “=right” will return the value in the cell to the right.

There are two things to note here. The first is that if you use this formula in the last column, you will get an error. Also note that Excel REQUIRES all named range cell references to be sheet qualified, so this range would need to be worksheet scoped.

Sheet1

If you select A1 on Sheet2 and define a named range “Sheet1” that equals Sheet1!A1 (again, no dollars signs) then the formula “=Sheet1” would return the value in Sheet1 that corresponds to your current cell. Note that if you use this formula on Sheet1, you will get a circular reference.

The one (COMPLEX) time I have used this:

Imagine a worksheet containing an unknown number of datasets. Each dataset is a “mini table” of 10 rows and 5 columns, and there is a blank row between each dataset. The first row of each data set has, in column 2, a reference id. In several places in the dataset, I need to use that reference number in a formula. One way to do this, is to use =a2 in the first dataset, =a11 in the second, =a23 in the third, etc. I don’t like this for several reasons. A COMPLEX alternative is to define a named range called “Dataset” with the following formula:

=OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/11)*11,0,11,5)”

Thus, the formula “=Dataset” will always return the 11×5 table (including the blank row) of the CURRENT dataset. This then allows you create (for example) a named range “ID” defined as:

=INDEX(ID,1,2)

which would always be the id from the current dataset. If you modify the worksheet to include more rows per dataset, or have the ID somewhere else in the dataset or whatever, then this would no longer work, but you would only have 1 place where you needed to modify it, rather than in numerous scattered formulae.

Combination

Of course you can combine relative and dynamic ranges, giving you an AWFUL (but effective) named range such as this:

=OFFSET(Sheet1!$A$1,TRUNC((row()-1)/MATCH(Sheet1!$A$1,OFFSET(Sheet1!$A$1,1,0,500,1),0))* MATCH(Sheet1!$A$1,OFFSET(Sheet1!$A$1,1,0,500,1),0),0, MATCH(Sheet1!$A$1,OFFSET(Sheet1!$A$1,1,0,500,1),0),COUNTA(Sheet1!$1:$1))

which would return the current dataset regardless of how many rows or columns there are in each dataset (as long as they are the same)

NOTE: Do NOT use relative named ranges from VBA. Just don’t.

UDFs

I don’t use many UDFs in Excel, but there are a couple that are particularly helpful in terms of Named ranges.

Blank

Returns the index of the first blank cell in a 1 dimensional range. This works exactly like the Match function, but looks for an empty cell (which you can’t do with match)

Public Function Blank(rData As Range) As Variant
    If rData.Columns.Count <> 1 And rData.Rows.Count <> 1 Then
        Blank = CVErr(xlErrNA)
        Exit Function
    End If
    If rData.Cells(1, 1).Formula = "" Then
        Blank = 1
        Exit Function
    End If
    If rData.Cells(IIf(rData.Columns.Count = 1, 2, 1), IIf(rData.Rows.Count = 1, 2, 1)).Formula = "" Then
        Blank = 2
        Exit Function
    End If
    If rData.Columns.Count = 1 Then
        If Not Intersect(rData.Cells(1, 1).End(xlDown).Offset(1, 0), rData) Is Nothing Then Blank = rData.Cells(1, 1).End(xlDown).Offset(1, 0).Row + 1 - rData.Cells(1, 1).Row
    Else
        If Not Intersect(rData.Cells(1, 1).End(xlToRight).Offset(0, 1), rData) Is Nothing Then Blank = rData.Cells(1, 1).End(xlToRight).Offset(0, 1).Column + 1 - rData.Cells(1, 1).Column
    End If
End Function

IndirectD

Allows the use of dynamic and non-contiguous ranges with indirect functionality

Public Function IndirectD(sRange As String) As Variant
    Set IndirectD = Application.Caller.Parent.Range(sRange)
End Function