Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot "row hide menu" keeps having items I don't have anymore!?!?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot "row hide menu" keeps having items I don't have anymore!?!?
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 On Jan 5, 12:11 am, Debra Dalgleish wrote: As answered in .excel.misc: In Excel 2002 and Excel 2003 you can programmatically change a setting to prevent old items from appearing. There is sample code he http://www.contextures.com/xlPivot04.html wrote: 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-- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -- Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot "row hide menu" keeps having items I don't have anymore!?!?
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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot "row hide menu" keeps having items I don't have anymore!?!?
Thank you Paul. It worked.
Ataru ha scritto: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot (Data Items "Count" vs "Sum") | Excel Discussion (Misc queries) | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
how to hide "All" in drop-down menu of my pivot table | Excel Discussion (Misc queries) | |||
Pivot "row hide menu" keeps having items I don't have anymore!?!? | Excel Discussion (Misc queries) | |||
Make "Worksheet Menu Bar" invisible or Delete/hide it's buttons | Excel Programming |