Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Purge old data
I have several pivot tables in a spreadsheet. There is a primary pivot table
called "Main" and many secondary Tables which use the Main table as the source. The data in Main keeps changing from week to week. However, I find that in pivot table drop downs there are values which do not exist in the current sheet. These values were there in the last week's data but not in this week's. Though they appear in the drop down, they dont appear in the table. Any idea as to why this is happening? How can I purge old data from the Pivot tables? Thanks Eshwar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Purge old data
Hi
To do it manually, just drag the relevant field out of the PT, do a Refresh, then drag the field back to its require position. To do it programmatically there is some code at Debra Dalgleish's site http://www.contextures.com/xlPivot04.html Regards Roger Govier Eshwar wrote: I have several pivot tables in a spreadsheet. There is a primary pivot table called "Main" and many secondary Tables which use the Main table as the source. The data in Main keeps changing from week to week. However, I find that in pivot table drop downs there are values which do not exist in the current sheet. These values were there in the last week's data but not in this week's. Though they appear in the drop down, they dont appear in the table. Any idea as to why this is happening? How can I purge old data from the Pivot tables? Thanks Eshwar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Purge old data
Thanks for the quick response. I did it manually and it worked. Since I have
more than 20 PTs I used the VBA macro. It works great! Eshwar "Roger Govier" wrote: Hi To do it manually, just drag the relevant field out of the PT, do a Refresh, then drag the field back to its require position. To do it programmatically there is some code at Debra Dalgleish's site http://www.contextures.com/xlPivot04.html Regards Roger Govier Eshwar wrote: I have several pivot tables in a spreadsheet. There is a primary pivot table called "Main" and many secondary Tables which use the Main table as the source. The data in Main keeps changing from week to week. However, I find that in pivot table drop downs there are values which do not exist in the current sheet. These values were there in the last week's data but not in this week's. Though they appear in the drop down, they dont appear in the table. Any idea as to why this is happening? How can I purge old data from the Pivot tables? Thanks Eshwar |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Purge old data
I cut and pasted the code at http://www.contextures.com/xlPivot04.html but
could not get it to work. I don't know VBA, btw. I right clicked the tab and got the code window and dropped in the code and saved the file. What else should I do? Bob "Eshwar" wrote: Thanks for the quick response. I did it manually and it worked. Since I have more than 20 PTs I used the VBA macro. It works great! Eshwar "Roger Govier" wrote: Hi To do it manually, just drag the relevant field out of the PT, do a Refresh, then drag the field back to its require position. To do it programmatically there is some code at Debra Dalgleish's site http://www.contextures.com/xlPivot04.html Regards Roger Govier Eshwar wrote: I have several pivot tables in a spreadsheet. There is a primary pivot table called "Main" and many secondary Tables which use the Main table as the source. The data in Main keeps changing from week to week. However, I find that in pivot table drop downs there are values which do not exist in the current sheet. These values were there in the last week's data but not in this week's. Though they appear in the drop down, they dont appear in the table. Any idea as to why this is happening? How can I purge old data from the Pivot tables? Thanks Eshwar |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Purge old data
The code will not run until you tell it to. From your worksheet, try
pressing Alt+F8, see if the macro appears in the list, and click Run. kayakonnor wrote: I cut and pasted the code at http://www.contextures.com/xlPivot04.html but could not get it to work. I don't know VBA, btw. I right clicked the tab and got the code window and dropped in the code and saved the file. What else should I do? Bob "Eshwar" wrote: Thanks for the quick response. I did it manually and it worked. Since I have more than 20 PTs I used the VBA macro. It works great! Eshwar "Roger Govier" wrote: Hi To do it manually, just drag the relevant field out of the PT, do a Refresh, then drag the field back to its require position. To do it programmatically there is some code at Debra Dalgleish's site http://www.contextures.com/xlPivot04.html Regards Roger Govier Eshwar wrote: I have several pivot tables in a spreadsheet. There is a primary pivot table called "Main" and many secondary Tables which use the Main table as the source. The data in Main keeps changing from week to week. However, I find that in pivot table drop downs there are values which do not exist in the current sheet. These values were there in the last week's data but not in this week's. Though they appear in the drop down, they dont appear in the table. Any idea as to why this is happening? How can I purge old data from the Pivot tables? Thanks Eshwar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Purge or transfer old data automatically? | Excel Worksheet Functions | |||
How do I purge data in Excel 2003 | Excel Worksheet Functions | |||
Purge data based on another Excel file??? | Excel Discussion (Misc queries) | |||
Purge functions leaving only calculated data? | Excel Worksheet Functions | |||
Purge Data Based on Cell Color and Time | Excel Programming |