Tuesday, October 26, 2004

Dynamic Range Names

If you have used a range name before then you will know that it can be frustrating updating the reference of the range name if you want to add data to the range.

Here are some ways you can dynamically update the range by using the OFFSET function in the range name reference.

Assume for all these examples that column A has a mixture of text and numbers for several cells.

Click Insert - Name - Define on the menu.
In the Names in Workbook Dialog box type a range name (Eg test_range) and then try these different options.

1: Expand Down as Many Rows as There are Numeric Entries.
    In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)

2: Expand Down as Many Rows as There are Numeric and Text Entries.
    In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

3: Expand Down to The Last Numeric Entry
    In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A))
If you expect a number larger than 1E+306 (a one with 306 zeros) then change this to a larger number.

4: Expand Down to The Last Text Entry
    In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

5: Expand Down Based on Another Cell Value
    Put the number 10 in cell B1 first then:
In the Refers to box type: =OFFSET($A$1,0,0,$B$1,1)
Now change the number in cell B1 and the range will change accordingly.

6: Expand Down One Row Each Month
    In the Refers to box type: =OFFSET($A$1,0,0,MONTH(TODAY()),1)

7: Expand Down One Row Each Week
    In the Refers to box type: =OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)
Requires the "Analysis Toolpak" to be installed. Tools>Add-ins-Analysis Toolpak

The good thing about number 3,4,5,6 and 7 is that they will include blank cells.

You can also change the Columns the dynamic range will span by simply changing the last Argument of the OFFSET function to a higher number than 1.
You could even expand across your Columns dynamically by placing another COUNT or COUNTA formula as the last argument, instead of 1. See below:

In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

This dynamic range will now also expand across Columns in Row 1. So if you add another Column to your Table the dynamic range will automatically incorporate it.

Adapted from OzGrid


Posted by: Jethro | 11:28 PM |