Dynamic Named Range

February 29, 2008

If you want to reference a range whose size changes (for pivot tables, for example)

Insert|Name|Define…

Name the range, and in the refers to box:

=OFFSET(’Sheet Name’!$A$1,0,0,COUNTA(’Sheet Name’!$A:$A),COUNTA(’Sheet Name’!$1:$1))

the $A$1 is the upper left of the range (change as needed)

The first “COUNTA()” is the number of rows, and the second “COUNTA()” is the number of columns.

THIS ASSUMES THAT THERE ARE NO BLANK CELLS IN COLUMN A!! If there are, you need to find another column with no blank cells, or insert one. Then just change the reference in the first “COUNTA()” to that column.

Posted in Excel