MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table Help


Posted by KC on September 18, 2000 6:45 AM

I have a series of data that I made a several pivot tables for. At the
end of each month I delete the data on the entry sheet to start the next
month. My problem is that the pull down boxes in the pivot tables
retains the options for data that is no longer present. Is there any
way to clear these choices in the pivot table so that only the present
data on the entry page is there? Or do I have to create new pivot
tables each time( which would be a pain, I have 14 different sheets with
tables on them)?


Posted by KC on September 25, 2000 1:39 PM

Unfortunately that hides the complete field, not just selected entries in the field. Still searching for a solution... :)

: I have a series of data that I made a several pivot tables for. At the : end of each month I delete the data on the entry sheet to start the next : month. My problem is that the pull down boxes in the pivot tables : retains the options for data that is no longer present. Is there any : way to clear these choices in the pivot table so that only the present : data on the entry page is there? Or do I have to create new pivot : tables each time( which would be a pain, I have 14 different sheets with : tables on them)?

Posted by XI on September 21, 0100 6:39 AM

Right click on the pulldown menu, then select Field. At the bottom of the window it should say
Hide items: and list the items in your pivot table. Select what option you would like to hide. Then click OK.

That should do it.

HTH

XI

I have a series of data that I made a several pivot tables for. At the end of each month I delete the data on the entry sheet to start the next month. My problem is that the pull down boxes in the pivot tables retains the options for data that is no longer present. Is there any way to clear these choices in the pivot table so that only the present data on the entry page is there? Or do I have to create new pivot tables each time( which would be a pain, I have 14 different sheets with tables on them)?

Posted by Michael Liu on September 18, 0100 1:21 PM

Is your new data contained in the exact same region
of the worksheet as the old data? If so, you just need
to refresh the pivot tables to key off of the "changed"
data. Right-click on the pivot table and click on "refresh".

If your data is a different range than the old data, you will
need to reassign the data range that your pivot tables are
referencing. This can be done programatically with a little bit
of code. end of each month I delete the data on the entry sheet to start the next month. My problem is that the pull down boxes in the pivot tables retains the options for data that is no longer present. Is there any way to clear these choices in the pivot table so that only the present data on the entry page is there? Or do I have to create new pivot tables each time( which would be a pain, I have 14 different sheets with tables on them)?

Posted by KC on September 19, 0100 3:01 PM

The new data is in the exact same location. The pivot table refreshes correctly, it's the pull down box for the date selection at the top of the pivit table that retains the old date information, even though it no longer exists on the data page. of the worksheet as the old data? If so, you just need to refresh the pivot tables to key off of the "changed" data. Right-click on the pivot table and click on "refresh". need to reassign the data range that your pivot tables are referencing. This can be done programatically with a little bit of code. : end of each month I delete the data on the entry sheet to start the next : month. My problem is that the pull down boxes in the pivot tables : retains the options for data that is no longer present. Is there any : way to clear these choices in the pivot table so that only the present : data on the entry page is there? Or do I have to create new pivot : tables each time( which would be a pain, I have 14 different sheets with : tables on them)?