dynamic named ranges in pivot tables (2000)

Topic

New Reply

Morning All,
I’ve been trying to apply a dynamic named range to a pivot table using:
=OFFSET($B$10,0,0,COUNTA($B$10:$B$65536),1)
where B10 is the first cell in the row field under the label.
Unfortunately, it doesn’t seem to change the range when the number of rows in the table changes.
I’d appreciate any thoughts on the subject cos its driving me mad!

Reply | Quote

Replies

Your formula works for me.

The only thing I can think of is: you haven’t got any blank entries in column b within the table have you?

Reply | Quote

No, no blank entries. It seems to work ok sometimes, but if the number of rows increases when I change the table the range size doesn’t always increase to match. Deeply puzzling… The formula works perfectly outside pivot tables.

Thanks

Graeme

Reply | Quote

>>>>>>>>if the number of rows increases when I change the table

is this by refreshing the background data, moving data fields around or unhiding certain items within a data field?

Reply | Quote

The table only changes when the user changes the page field using the dropdown; the page field is Week Number and the number of items in the row field changes from depending on which week the user selects. The background data doesn’t change. Still baffled!

Graeme

Reply | Quote

A pivot table will not automatically update when the th eunderlying data changes. You do need to refresh the pivot table after any data addition. You could of course have a worksheet change event carry out the refresh.

Andrew C

Reply | Quote

The underlying data doesn’t change; both it and the pivot table are refreshed on file-open. All that changes is the user selects a different week from the Week Number pagefield drop-down.
On a similar subject, when the table is changed the Worksheet_Change event doesn’t fire; is this the way it should work or am I missing something?

Reply | Quote

Yes, that is the way it should / does work. However the Worksheet_Calculate event should fire when you change the PT view using page fields etc or when you refresh even if the underlying data has not changed.

Andrew

Reply | Quote

I’ve got a pivot table running here to try and duplicate the problem, and I can’t. When I change the week on display the range resizes. What are the chances of you posting a cut down version of the book?

and yes, that is the way it works for me too – the change event doesn’t fire.

Reply | Quote

I would send the workbook, but it seems to be working fine now and I can’t duplicate the problem either! Still don’t understand what happened, but thank you both for your help. And thanks, Andrew, for the tip about the calculate event; exactly what I needed.

Graeme

Reply | Quote