Dynamic named ranges

Huge power can result from combining the OFFSET function with Named Ranges in Excel. Here's how.

OFFSET itself is phenomenally powerful. It allows you to refer to a range of a chosen height and width that is a chosen number of rows and columns away from another range. Its syntax (or one of two possible syntaxes) is:

=OFFSET(range, rows, columns, height, width)

Range is the original range from which you're offsetting, which only actually needs to be a single-celled range. Rows and columns are the respective offsets (measured in cells), while height and width define the height and width of the new range.

So OFFSET(A1,2,3,4,5) will give you a range 4 rows high, 5 rows across that starts 2 rows below and 3 columns to the right of A1 (i.e. D3:H6).

Named ranges are also great. You can reference them in formulae, and if they change in dimensions, all you need to do is change the reference behind the named range, and all of your formulae update accordingly. Fantastic!

But what if you know in advance that your referenced range is going to change? And what if the height and width of the range can be determined based on the contents of a column and row?

So let’s combine the two. Let’s assume you have a range of data that starts in A2, with column titles in row 1. And let’s assume that your columns and rows are contiguous (i.e. no blank rows or columns), and a column title signifies that it should be part of the range, while an entry in column A qualifies that row as being part of the range.

Create a named range (Formulas | Define Name) called my_data. And name the range as follows:

=OFFSET(A2,0,0,COUNTA(A:A)-1,COUNTA(1:1))

(Note, the zeros mean that we’re not actually using the offset bit of the function, but that’s by the by.)

The range referenced will start in A2, will (if the earlier assumptions hold) end at the bottom-most row which has a value in column A and go as far right as there are columns with a title. The "-1" adjustor is to account for the title in column A, which you don’t want to count. The COUNTA function simply counts the non-empty cells in a range.

By having a dynamic range, you can add rows and columns to a range that is being looked up in without fear of rows or columns being missed out of the look-up. So a database of contacts can be added to and the new names will be included in any referenced range; and new columns of data can be added with similar confidence.

• Tweet