Removal of "old" drop-down menu items in pivot table.


Posted by Mark Lyons on February 22, 2001 8:26 AM

I've researched this problem for days, but cannot find what should be a simple solution. Please help!

For some reason, items that were part of the "old" database are still showing up in the Pivot Table Buttons drop down menu (when the fields are located in the "Row" or "Column" areas of the pivot table layout ... not in the "Page" area).

For example, if the pivot table source data contained a field called "Region" and one of the items was "Southwestern" it should show up under the drop-down "Region" button menu if, indeed, it is included in at least one record of the database. On the other hand, if the item "Southwestern" is no longer an item under the "Region" field (in any record), it SHOULD NOT show up under the drop-down "Region" button menu any longer (at least in my opinion) ... but it does! My actual problem is much larger because the data changes constantly ... because of this I end up with maybe 100 possible drop down button menu choices even though the actual possible choices should be 25-30. You would think a "Refresh" command would immediately correct this problem, but it does not. I am using Excel 200. What am I doing wrong?

Thank you!

P.S. I have a simple test workbook that I can send to anyone who wants to see the "problem in action".



Posted by Mark W. on February 22, 2001 8:54 AM

Mark, you're not doing anything wrong. Your
PivotTable has "learned" that Southwestern is
in the domain of Region. If you want your
PivotTable to "re-learn" the Region domain you
need to create a new PivotTable or ...

1. Redefine your data list without the Region
column
2. Refresh your PivotTable
3. Redefine your data list with the inclusion
of the Region column
4. Refresh your PivotTable and restore the Region
field to your PivotTable's ROW or COLUMN area.