Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

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!


Pivot Changes

Posted by Richard Jones on February 20, 2001 5:13 AM
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


Re: Pivot Changes

Posted by Mark Lyons on February 20, 2001 6:42 AM
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


Re: Pivot Changes

Posted by Richard on February 20, 2001 8:06 AM
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

Re: Pivot Changes

Posted by Mark Lyons on February 20, 2001 10:01 AM
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

Re: Pivot Changes

Posted by Richard on February 21, 2001 12:39 AM
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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.