» Automatically Updating a Range Name Reference in Excel 2007

Updating the size of range Name is necessary when using Names in formulas (such as VLOOKUP or INDEX) and as a source data for PivotTable Report.

To automatically update a range Name:

Insert the following formula into the Refers to box in the New Name dialog box:
=OFFSET(Ref,0,0,COUNTA(ColumnA),COUNTA(Row_1))
This OFFSET formula returns the range address calculated by measuring the width (number of rows) and the height (number of columns) of a reference.



The syntax of the OFFSET formula is: (Reference,Rows,Cols,Height,Width).

Reference: The reference from which you want to base the offset (in the example the reference is A1).
Rows,Cols: The number of rows or columns that you want the upper-left cell to refer to (for this example, 0 rows and 0 columns).
Height,Width: The height or width, in number of rows/columns that you want the returned reference to be, the numbers must be positive.

To measure the Height or Width size use the COUNTA function. The COUNTA function returns the number of non blank cells in a range.