Problem: Pivot Table Filter Choices
Version: Excel 2007 / Windows 7
I've created a situation where data is established in a single worksheet for use as the data source of a pivot table on another worksheet.
I haven't been able to have ONLY the new dataset's data to be reflected in the Value Filter choices in my pivot table however.
For example, say the dataset contains a date field and you're using that as a Column in the pivot table. Then you go to do a Filter on the column in the pivot table. It shows all the dates available in the dataset for you to choose from to create your filter. That's great.
If my first dataset contained dates from today through 31-Aug-2011 and I then recreated the dataset in the source sheet to only have dates until 31-MAY-2011, my pivot table results show correctly but in the choice for filter values, I'll still see the dates out through 31-AUG as being available as valid choices to filter on, even though no such dates exist any longer in the data source.
I've done pivotcaches(1).refresh in my code, and manually done Data Refresh for the pivot table from the ribbon, but I can't seem to make the filter value choices reflect the currently available data in the dataset.
What am I missing?
Reply With Quote
