In Excel, named ranges are a convenient way to reference a continous range of cells using a descriptive name instead of a complicated expression or formula. However, when the referenced range is not static, and the number of rows and/or columns is likely to change between updates of the dataset in question, a simple range reference will only be useful for as long as the dataset fits the range’s dimensions at that time. As additional rows or columns are added, the named range no longer sufficiently references the dimensional span of the dataset.
However, dynamic datasets can be referenced with a named range. The key is to avoid explicit cell references in the definition.
Suppose that our dynamic dataset will reside in
Sheet1, and the first cell,
regardless of the ultimate depth and breadth of a particular iteration of the
dataset , will be
A1. Also, at any given time, the number of columns and
rows in the dataset will be greater than or equal to 1.
To declare dynamic range reference, enter
Ctrl + F3 to open the
Name Manager, and select
New... in the upper left-hand side. Specify a
name for the range in question, and a description in the
(if desired). In the
Refers to: section, enter the following:
Note that OFFSET has the following declaration:
OFFSET(reference, rows, cols, [height], [width])
- reference is the reference from which you want to base the offset. It must refer to a cell or range of adjacent cells; otherwise, OFFSET returns #VALUE!.
- rows is the number of rows, up or down, that you want the upper-left cell to refer to.
- cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to.
- height is the height, in number of rows, that you want the returned reference to be.
- width is the width, in number of columns, that you want the returned reference to be.
We set reference to
$A$1, and rows and cols to
0, since the named
range refers to
$A$1 as the static point.
COUNTA($A:$A) returns the number
of consecutively populated cells in column
A starting from
working down; COUNTA(\(1:\)1) returns the number of consecutively populated
cells in row
1 starting from
$A$1 and working right. Upon return, the call
to OFFSET will yield the dimensions of the continuous range of cells
starting in cell
$A$1, whatever those dimensions may be. With this
formulation, you’ll be certain that when you call a named range, what’s
actually being referenced and what you’re expecting to be referred will be one
in the same.
There may be times when only one dimension of a dataset will be expected to vary between updates: Either the number of columns or the number of rows, but not both. In that case, the named range definition is a simplified version of the 2-varying dimensions named range.
To facilitate a varying number of columns between updates, specify the named range as:
To facilitate a varying number of rows between updates, specify the named range as: