Dynamic Named Ranges

Creating Dynamic Named Ranges is a fundamental part of much of the work I do, and hopefully the explanation and examples below provide a good start point for using them.The basic idea is that you create a Defined Name range with an offset formula in the Refers To box i.e. =OFFSET(reference,rows,cols,height,width). The instructions below assume that you have at least one column filled with either numeric, text or blank entries (apart from the first example).

Start by selecting the menu Insert>Name>Define and provide a name in the Names box e.g. rMyRange, then with the following examples the formula is simply entered into the Refers To box, as stated above:

  1. Expand down in a single column for as many rows as there are numeric entries:
    =OFFSET($A$1,0,0,COUNT($A:$A),1)
  2. Expand across in a single row for as many columns as there are text and numeric entries:
    =OFFSET($A$1,0,0,1,COUNTA($1:$1))
  3. Expand down for as many rows as there are text and numeric entries:
    =OFFSET($A$1,0,0,COUNTA($A:$A),1)
  4. Expand down and across for as many columns and rows as there are text and numeric entries:
    =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
  5. Expand down in a single column to the highest value numeric entry:
    =OFFSET($A$1,0,0,MATCH(1E+100,$A:$A,1),1)I’ve rashly assumed that you’re unlikely to be using numbers larger than 1E+100 (a 1 followed by 100 zeros, not quite a googol but sufficient for my needs), but if you insist on playing with larger numbers than I ever do, just amend this to the largest value you think you’ll ever need plus 1.
  6. Expand down in a single column to the last text entry
    =OFFSET($A$1,0,0,MATCH(“*”,$A:$A,-1),1)
  7. Expand down based on a cell value e.g. using cell B1:
    =OFFSET($A$1,0,0,$B$1,1)
  8. As per option 3 but excluding the header row:
    =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

Note that this is by no means a definitive list and the permutations are limited only by your imagination in how you want to use them. For instance the reference used above ($A$1) could be replaced with a single cell named range e.g. rMyRangeStart; the height or width can be replaced with formulae, as could the row and column offsets , etc.

The Excel help file provides the following explanation for the OFFSET formula:

OFFSET(reference,rows, cols,height,width)

Reference: is the reference from which you want to base the offset. Reference must be a reference to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.

Rows: is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the Starting reference) or negative (which means above the Starting reference).

Cols: is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the Starting reference) or negative (which means to the left of the Starting reference).

If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.

Height: is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width: is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.

If height or width is omitted, it is assumed to be the same height or width as reference.

Applies to: Excel 2003, 2007. Not tested: Excel 2010

Advertisements

2 thoughts on “Dynamic Named Ranges

  1. Pingback: Defined Names in Excel | More from…

  2. Pingback: Combining Cell Contents (Multiple Cells) | More from…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s