# Dynamic Named Ranges

*by Jon Wittwer*4/29/2009

In Excel, a *dynamic range* is a range that can change based on user input. For example, you may want to refer to only the cells in a column that contain data instead of the entire column. Or, you might want to allow the size of the range to be controlled by the result of some other function.

In an earlier article, "Create a Drop-Down List using Data Validation," I explained that if you use a **named range**, you can reference a list that is on a separate worksheet. That is just one use for a named range. There are hundreds of others.

To create a **dynamic** named range, you use a formula in the "Refers to" field instead of just a range reference. Dynamic named ranges are great for:

- Customizable lists for drop-down boxes
- Print areas that change based on user input
- Charts with variable-length data sources

In this article I will explain the formulas used to create a **dynamic named range**. 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).

## The OFFSET Function

The most common function used to create a Dynamic Named Range is the OFFSET function. It allows you to define a range with a specific number of rows and columns. The syntax for the OFFSET function is:

*reference*,

*rows*,

*cols*,[

*height*],[

*width*])

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.

### Example:

In the meal planner, I have a list like the image shown to the right that 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 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:

*row_num*,1)

where *row_num* = 15.

To create a **dynamic named range**, you use a formula for the *row_num* 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 many ways to accomplish this (using COUNTA, COUNT, MATCH 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.

## Use INDEX for a Non-Volatile Dynamic Range

In some cases, like large files with a lot of formulas, using OFFSET can be a problem because it is a *volatile* function. A *volatile* function is calculated every time any cell in the worksheet recalculates (instead of just when one of the function's arguments change).

I learned how to use the INDEX function to make a non-volatile dynamic range from from Mynda Treacy here and Debra Dalgleish here.

The INDEX function can return a cell reference, so you can create a dynamic named range using the formula:

*row_num*,1)

Just like with the OFFSET function, the *row_num* value can be controlled via user input or some other function, and that is why these formulas are considered *dynamic*.

## The INDIRECT Function

The INDIRECT function is another function that allows you to create a dynamic range. However, it is also a *volatile* function, so I won't discuss it other than to show the formula that returns the same range as the examples above:

*row_num*)

## 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.

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.

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.

## Expand the Range to the LAST NON-BLANK Value

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

Note that the empty string "" is NOT blank.

## Expand the Range to the LAST NON-EMPTY Value ""

In many practical applications, we may not necessarily want to find the last non-*blank* cell ( meaning no values or formulas), but instead may only want to find the last non-*empty* cell.

If we define "empty" as the empty string "", then we can return the last non-empty VALUE using the formula below. This allows us to use formulas in column A that may return an empty string.

The SUMPRODUCT function allows you to avoid entering the formula as an array formula (not having to use Ctrl+Shift+Enter). Inside the SUMPRODUCT function, we're multiplying the boolean (0 or 1) result for the $A:$A<>"" comparison by the row number, so that the maximum result is the row number of the last blank cell.

### References Related to Dynamic Named Ranges

- Dynamic Named Ranges at
*ozgrid.com*- Read through this page for other formulas and examples related to dynamic named ranges.

**Disclaimer**: This article is meant for educational purposes only.