MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I remove "old" drop-down menu items in pivot table?


Posted by Mark Lyons on February 19, 2001 4:10 PM

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. 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). 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!


Posted by Richard Jones on February 20, 2001 5:13 AM

Pivot Changes

Just a suggestion - have you checked the area that the pivot is drawing the data from.

Go to the wizard and select back to check the area - then do a refresh.

Also, check the advanced settings on the pull down item to see how you are sorting the selections

Posted by Mark Lyons on February 20, 2001 6:42 AM

Re: Pivot Changes

Richard,
Thanks for the suggestion. I tried what you said but, unfortunately, it didn't have any effect.

I have spent countless hours trying to make the "old" menu items go away ... I have set all the various options in every conceivable combination to try and make these "old" menu items go away. Additionally, I have made absolutely sure that the new database is, indeed, new and doesn't contain the "old" menu items. I even went to "Table Options..." and unchecked the "Save data with table layout" option (thinking that this might "purge" the old data when I closed the worksheet). By the way, this problem exists with all of the Pivot Table fields, not just a specific field I'm having trouble with.

Thanks,
Mark

Posted by Richard on February 20, 2001 8:06 AM

Re: Pivot Changes

Have you got any information grouped or hidden, i have had strange problems when grouping data such as dates...which has then left me unable to do anything else until taking off the grouping

Posted by Mark Lyons on February 20, 2001 10:01 AM

Re: Pivot Changes

Richard,
There is no information grouped or hidden ... as a matter of fact, I have built the pivot table from scratch several times to make sure that I haven't inadvertently done such an action.
Thanks again!
Mark

Posted by Richard on February 21, 2001 12:39 AM

Re: Pivot Changes

If you want me to have a look you can send it to me at rjones9251@hotmail.com..

see if i can get anywhere!!

richard