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:

=OFFSET(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:

Meal List WorksheetIn 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:

=OFFSET($A$1,0,0,15,1)

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

=OFFSET($A$1,0,0,MATCH(9.99999999999999E+307,$A:$A),1)

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

=OFFSET($A$1,0,0,1,MATCH(9.99999999999999E+307,$1:$1))

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.

=OFFSET($A$1,0,0,MATCH(REPT("z",255),$A:$A),1)

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

=OFFSET($A$1,0,0,1,MATCH(REPT("z",255),$1:$1))

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.

=OFFSET($A$1,0,0,MAX( MATCH(9.99E+307,$A:$A), MATCH(REPT("z",255),$A:$A) ),1)

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.

=OFFSET($A$1,0,0,SUMPRODUCT(MAX(($A:$A<>"")*(ROW($A:$A)))),1)

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.

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Dynamic Named Ranges" from Vertex42.com

Disclaimer: This article is meant for educational purposes only.

Like This Page?

Become a Fan

       
Master Excel - Spreadsheet Tips Workbook

Create Dashboard Reports in Excel!