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.

[Aladin Akyurek]

It should after a refresh.

Is your column A of numeric type?

[ This Message was edited by: Aladin Akyurek on 2002-05-03 09:45 ]

[GeorgeB]

Aladin
No Col A contains string only

[Aladin Akyurek]

On 2002-05-03 11:06, GeorgeB wrote:
Aladin
No Col A contains string only

Is there one which is numeric (numbers, dates, time values)?

[GeorgeB]

Yes indeed. this is a table containing Names, Places and amounts. A credit card expenditure report.

[Aladin Akyurek]

On 2002-05-03 11:21, GeorgeB wrote:
Yes indeed. this is a table containing Names, Places and amounts. A credit card expenditure report.

George,

Just switch from A to the amount column (probably C in your case) in

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

you'll be OK.

Aladin

[GeorgeB]

Do you highlight a specific range or cell? If so which?

[GeorgeB]

OK Got it working. Thanks a million Aladin

[Aladin Akyurek]

On 2002-05-03 12:07, GeorgeB wrote:
Do you highlight a specific range or cell? If so which?

No. No need for that. Just try to follow the steps you quoted. Don't forget using the sheet name of the worksheet where your data is.

By the way, 5 in

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

refers to the number of columns the data area of interest covers: The above formula covers columns A to E, E being the 5th column.

[ This Message was edited by: Aladin Akyurek on 2002-05-03 12:32 ]

[GeorgeB]

One more problem.
It worked well on a test book but when I tried to put it to use on the real workbook the pivot table selected 59 rows below the end of the table.