|
Dynamic Named Ranges
Rodney POWELL
Microsoft MVP - Excel |
|
Named ranges are among the most powerful features of Excel, especially
when used as the source range for list controls, PivotTables, or charts.
A problem arises, however, when the contents of a list change often.
It would be a problem to have to redefine your named ranges everytime a
table has records added or removed. The solution is to create a range that
will automatically adjust based on the number of items in the list.
First, create a list in column A of a worksheet.
If you are working on a version EARLIER than Excel 2007 :
From the worksheet's Insert menu choose Names then the
Define.... Enter a name for your new range, such as
MySheet!rngDynamic. Then, in the Refers to: box,
enter the following:
=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)
The user interface changed with Excel 2007. So instead, from
the Formulas menu go to the Define Names group and
create this named range fron the Define Name command.
How It Works:
The first argument for the OFFSET function is the cell on
which you want to anchor it. Everything else will be set relative
the this cell address. Typically, you will want it to be either
the header for the first field in your source data table or its
first record.
The second argument indicates how many rows to move from the
anchor address to begin the range. In this case, we used 0 which
would include my header row. If we substituted 1 then the header
row would be skipped and the range would begin on row 2 of the table.
The third argument indicates how many columns to move from the
anchor address to begin the range. In this example we used 0,
meaning to begin the range on the same column as the anchor address
provided in the first argument.
The fourth argument tells how many rows the range should extend.
They key here is to substitute the COUNTA function for your
primary field, instead of hardcoding a value. This way if you add
or remove items from that field, the range will grow or shrink
accordingly. You also may need to subtract 1 from the COUNTA
result to account for the elimination of a header row. It is
important that you do not have any superfluous data beneath the
table range you intend to evaluate.
The fifth, and final, argument is how many columns wide you want
for the dynamic range to be. In our example, this range includes
only a single column, therefore the argument provided was simply 1.
This is a very flexible technique for defining your named ranges.
The best thing to do is to experiment with some variations of the
sample formula provided and you will soon find that Dynamic Named
Ranges will become an indispensable tool you will want to use
throughout your Excel work.
Note:
Dynamic named ranges can only be referenced in an open workbook,
because they are dependent upon a workbook's ability to calculate.
Know an even better way?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
