Pivot table: filter dates and group by year

In Excel 2007, I am trying to set up four separate pivot tables. All are based on data range "AllData!Table1". This is a table of projects and the reports submitted on those projects. The reports needed to meet four separate date milestones, and I'm trying to create a separate pivot table against each milestone to show how many were late.

All pivot tables have the same basic format:
- Row labels are: ProjNum and FDate
- Report Filter is: Status ("done")
- Values is: Count of FDate
- Column Label is different: Late MS1, MS2, MS3, or MS4

Each pivot table displays with the ProjNum as a group heading, with totals across that row, and dates below with totals on each row.

I would like to filter the dates for each pivot table on one sheet as "before 1/1/2013" and then group by year.

Obviously, I'm losing!

When I set the Date filter, all my dates change to display "<1/1/2013".
Plus, making a change in one pivot table affects all four!
EDIT: Not only that, but changes affect for other pivot tables on the next sheet!!

Can someone please explain what I'm doing wrong and how to do it right?

Thanks!
Ed

Re: Pivot table: filter dates and group by year

Quote

If you have already created the new PivotTable, you must delete the existing PivotTable, and then create another new PivotTable, using the steps above.

No way to clear or disconnect the memory cache?! That bites!!
Yes, I created one pivot, and then copied it and just changed the one field.
Well, I guess it's a good way to learn things.
Or sumpin' like that!!

Thanks for the info.
Ed

Re: Pivot table: filter dates and group by year

Sorry - this isn't helping as much as I thought. Possibly because I'm using XL2007?
According to this KB >> APPLIES TO:
Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 98 for Macintosh

I created a new pivot table from scratch on a different worksheet. But yes, it does refer to the same data table on a sheet in this workbook.

And when I grouped these dates by month, lo and behold it also changed all four pivot tables on another sheet, which had been grouped by year!

Fortunately, I did find an Ozgrid reference to this:
http://www.contextures.com/xlPivot07.html#Another
This workaround did work and fixed my issue.

*whew!!*
Ed