Pivot "row hide menu" keeps having items I don't have anymore!?!?

A

atarumorooka

Hello, my excel 2003 file manages expences and has columns for date,
amount,
account and type of expence. This one for example had values like
clothes, food, toys and so on.
As I understand the pivot "row hide menu" gets items from the data
page values so I'll find correctly clothes, food, toys....and I can
select on or more of them to be displayed in my pivot.

PROBLEM: The question is...when I cancel all the expences for food I
expect to have "food" item cancelled also from the hide menu...but it
keeps staying there and that is very annoying because I suppose the
number of items will increase with the time to an amount hard to
manage.


The page field has a property to hide the items from the "hide menu"
drop down list while the row and column fields haven't.


Any idea how to hide items or update them so to automatically sync with

the values of the main sheet?


English is not my language but I hope I was clear enough to make you
understand what is my question!!!!


Thank you,
Ataru
 
A

atarumorooka

Debra, thank you for the help...that is great. I still wonder why that
feature (for me a problem) is still there in Excel 2003.
Hope won't be in the next release.
Ataru
 
P

paul.bellrichard

Excel keeps a cache of indexes.
To clear this cache you have a few options (solutions borrowed from
http://www.contextures.com/xlPivot04.html):

A - Delete the old pivot table and create a new one.
B - Drag the field row out of the pivot table --> Refresh --> Drag back
C - Reset the PivotTable cache (See VBA code below)


Note: The following code was lifted directly from
http://www.contextures.com/xlPivot04.html

<<<<<<<<<<<<<< Begin VBA Code >>>>>>>>>>>>>>>
Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

'in Excel 2002 and later versions
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

End Sub
<<<<<<<<<<<<<<< End VBA Code >>>>>>>>>>>>>>>>>>

--Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top