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

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

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.

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.