Dynamic Named Ranges

I recently wrote an article about how to Create a Drop-Down List using Data Validation explaining that if you use a named range, you can reference a list that is on a separate worksheet. In this article, I will explain the formulas used to create a Dynamic Named Range which will allow a list to expand or contract simply by adding or deleting data.

I'm not sure where this technique got its name, but Dave Hawley of OzGrid.com is also a big fan (see his article on the subject). To see this technique in action, take a look at my Meal Planner template.

The key to creating a Dynamic Named Range is the OFFSET function, which allows you to define a range with a specific number of rows and columns. The syntax for the OFFSET function is:

The rows and cols values tell the function where you want the upper-left cell of your range to be, relative to the reference cell. The height and width values tell the function how many rows and columns you want to include in your range.

In the meal planner, I have a list like the image shown to the right which I use as the reference for a drop-down list on another worksheet. To do that, I need to define a named range that will include all the items in column A. I could define the range as $A:$A (the entire column), but then I'd end up with a bunch of blank cells at the end of my drop-down list.

Using the OFFSET function, I instead define a named range that starts at cell $A$1 and ends at the cell containing the last value in the column. If the last value in the column was on row 15, the formula for the named range would be:

To create a dynamic named range, you use a formula for the height value to specify how many rows you want the range to include. Luckily, there are some formulas that will return the position of the last NUMERIC value, last TEXT value, or last NON-BLANK value in a column. There are actually many ways to accomplish this (using COUNTA, COUNT, or various array formulas), but you need to be aware that some formulas may not work if the data include blanks, error values (like #N/A or #DIV/0!), or data of a different type.

Expand the Range to the LAST NUMERIC Value

The following formula will allow the range to include blanks, error values, and text values, but the range will only extend to the last NUMERIC value. See Return the Last Numeric Value in a Column.

To create a dynamic named range that extends to the last numeric value in a ROW (row 1 in this case) ...

The value 9.99999999999999E+307 is really kind of overkill, but it is used because it is the largest numeric value that can be stored in a cell.

Expand the Range to the LAST TEXT Value

The following formula will allow the range to include blanks, error values, and numeric values, but the range will only extend to the last TEXT value. See Return the Last Test Value in a Column

To create a dynamic named range that extends to the last numeric value in a ROW (row 1 in this case) ...

Note: A formula that returns the empty string "" is not BLANK. It is treated as a TEXT value: ISBLANK("")=FALSE.

Using REPT("z",255) for the lookup value is not fool proof. The Greek, Cyrillic, Hebrew, and Arabic Unicode characters (and possibly other Unicode characters) come after z in the sort order. See this article for more info.

Expand the Range to the LAST NON-BLANK Value

To expand a range to the last non-blank value, we can simply use the largest position returned by either of the two formulas listed above.

Note that the empty string "" is NOT blank.