DYNAMIC NAMED RANGE

[GeorgeB]

DYNAMIC NAMED RANGE
I have tried to get this to work using instructions from Dave Hawley’s site and from instructions posted here. The most recent being by Aladin (below)
The range expands if you insert a row in the range but an ordinary named range will do that. A pivot table will not see anything added to the bottom of the table. What am I doing wrong?

[1] activate the option Insert|Name|Define,
[2] enter EndRow as name in the Names in the Workbook box,
[3] enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,x!$A:$A)

[ Note. Replace 'x' by the sheetname where the database is. ]

[4] Activate Add,
[5] Enter Database as name in the Names in Workbook box,
[6] Enter as formula in the Refers to box:

=OFFSET(x!$A$1,0,0,EndRow,5)

[ Note. Replace 'x' by the sheetname where the database is. ]

[7] Activate OK.

Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area.

 

[GeorgeB]

By the way my table starts in row 6o and instead of =OFFSET(INPUT!$A$1,0,EndRow,6) I used $A$60 instead.

 

[Aladin Akyurek]

On 2002-05-03 13:00, GeorgeB wrote:
By the way my table starts in row 6o and instead of =OFFSET(INPUT!$A$1,0,EndRow,6) I used $A$60 instead.

=OFFSET(INPUT!$A$60,0,EndRow-59,6)

will do. In other threads, I added an additional mechanism to variablize that minus 59 bit.

The most important thing is that the defined name, when that name must be used with Pivot Tables, D-functions (DSUM, etc), Advanced Filter, includes the row that houses the labels/column headings.

Aladin
This message was edited by Aladin Akyurek on 2002-05-03 13:40

 

[GeorgeB]

You're the man Aladin. Thanks again.