MrExcel Publishing
Your One Stop for Excel Tips & Solutions

phantom records in pivot tables !?


Posted by Roger on May 31, 2001 12:03 PM

Hello,
Sometimes I enter data for a particular Product Category and refresh a given PivotTable sheet. Later, I may be asked to eliminate that Product
Category (especially in case of Plan figures).
- Once I have entered a Product Category, say 'Catgy A', it gets
reflected in the PivotTable.
- Later I eliminate it / rename it as 'Catgy A1' (effectively removing
the 'Catgy A' from the database)
- Now when I refresh the PivotTable worksheet, the new 'Catgy A1' is
shown in place of 'Catgy A'.
- However, when I click on the drop-down list in the Product Category
field of the PivotTable, the first 'Catgy A' is still shown as an item
(which has no data)

My questions --
* How can I erase all trace of the 'Catgy A' item from the PivotTable's
'memory' / cache ?
* Is there any solution besides re-constructing the PivotTable ?
I mean, besides 'hiding' it by using the Field Settings dialog box, is
there any other way, such that it doesn't show even in that dialog box ?

I also tried putting off the 'Save data with table layout' in PivotTable
Options. But that didn't work.


Posted by Mark W. on May 31, 2001 12:05 PM

> Is there any solution besides re-constructing the PivotTable?

No.

Posted by Mark W. on May 31, 2001 12:39 PM

And, BTW...

What you're describing really isn't a phantom record.
PivotTables remember the values of a given field
that makeup the domain of that field. This isn't
a "bug" or design flaw -- it's necessary to support
fixed dimension PivotTables.

Posted by Roger on June 01, 2001 11:49 AM

Re: And, BTW...

hi Mark thanks for the answer
so if you clean up ( select the database sheet and delete the records)
and import ( copy paste) new records the previous data are still"remembered" by excel ?

Roger

Posted by Mark W. on June 01, 2001 12:54 PM

Re: And, BTW...

> ...the previous data are still "remembered" by
> excel ?

Yes, but not by Excel -- it's the PivotTable that
"remembers".