Pivot Table Drop Downs
When I apply a pivot table template to a new dataset, I always still have the
old field values in the drop down list. Anyone know how to "clear" the old values from a prior dataset when using a pivot table for a new dataset? For example, is column "A" is a filed named "Reference #" and held the values 1-20 and I used that field in a Pivot Table then the drop down for this field would have 1-20. If I then use a dataset that has "Refernce #" range of 100-199, I would expect only 100-199 to appear in the Pivot Table drop down for that field. However, the drop down would contain 1-20 AND 100-199 (1-20 from the first dataset and then 100-199 from the second). How can I get the Pivot drop down to ONLY display the values for the current dataset? Pivot tables seem to like to remember any prior values for some reason. -- Thanks, Chuck |
Pivot Table Drop Downs
Check out this link...
http://www.contextures.com/xlPivot04.html -- HTH... Jim Thomlinson "ChuckTheDuck" wrote: When I apply a pivot table template to a new dataset, I always still have the old field values in the drop down list. Anyone know how to "clear" the old values from a prior dataset when using a pivot table for a new dataset? For example, is column "A" is a filed named "Reference #" and held the values 1-20 and I used that field in a Pivot Table then the drop down for this field would have 1-20. If I then use a dataset that has "Refernce #" range of 100-199, I would expect only 100-199 to appear in the Pivot Table drop down for that field. However, the drop down would contain 1-20 AND 100-199 (1-20 from the first dataset and then 100-199 from the second). How can I get the Pivot drop down to ONLY display the values for the current dataset? Pivot tables seem to like to remember any prior values for some reason. -- Thanks, Chuck |
Pivot Table Drop Downs
One fast answer is to create a macro that generates the pivot table.
When you open a new data set, run the macro, and it will create a fresh table. |
Pivot Table Drop Downs
The Contextures method can be simplified. Just remove the field from
the Pivot Table layout that holds the old records, then refresh the table, then drag the field back in. You can also create a simple macro to do this. |
All times are GMT +1. The time now is 04:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com