Pivot Tables with different groupings based on same data table

[billwoodard]

In Excel 2007 I have one data table, say Dates by Weight, and want to see and
save two pivot tables & charts, one grouped by weeks, the other by months. If
I simply change the date grouping in one table, the other table also changes.
I could create two separate data tables, but I'd like to be able to add new
data to only one table. How can I keep the groupings in the 2 pivot tables
distinct without losing the ability to update each of them when the main data
table is updated?

Thanks for any tips.

 

[Ian Grega]

Bill, you can group the same initial field multiple times.

If you initially group your dates into weeks this becomes Group 1 (which you
can rename to weeks) if you then Group this Group 1 (Weeks) into months then
you get Group 2 (which you can rename to months). You can then have one pivot
table showing the data by weeks and the other by months.

Alternatively, you can add an extra dummy column to the data for the second
pivot table that you create, and Excel will after a couple of actions treat
each table separately.

 

[Ian Grega]

And alternatively again you could add two more columns to your data one using
the weeknum function and the other the month function so that you don't have
to manually update the groupings.

 

[billwoodard]

Bill, you can group the same initial field multiple times.

If you initially group your dates into weeks this becomes Group 1 (which you
can rename to weeks) if you then Group this Group 1 (Weeks) into months then
you get Group 2 (which you can rename to months). You can then have one pivot
table showing the data by weeks and the other by months.

Alternatively, you can add an extra dummy column to the data for the second
pivot table that you create, and Excel will after a couple of actions treat
each table separately.

Perhaps I didn't make the situation clear. On a single worksheet I have a
small table consisting of two columns, Date & Sales. Each row is one
instance of a date with its sales. The data is in the form of an Excel 2007
"Table". With a cell in the table selected, I click on "Insert Pivot Table";
position it on the same worksheet as the data table; in the Pivot Table Field
List I check both Date and Sales; then select a Date cell, Rt.Click , Click
Group, and Group by weeks (7 days); thus giving me the 1st table I want and
from which I can obtain the chart I want.
But then I try to repeat the whole process, same worksheet, same sequence,
and I get a 2nd pivot table. And now when I group the dates by Months, the
1st pivot table suddenly changes to a monthly table as well & likewise a
chart associated with it. How do I keep these two pivot tables independent
of eachother??????

 

[ColG]

I am having the same problem - did anyone find a solution?
I would really appreciate any help on this.

Thanks

 

[billwoodard]

I am having the same problem - did anyone find a solution?
I would really appreciate any help on this.

Thanks


:
Go to "Excel New Users" & search for "billwoodard" and you'll find a useful solution. The last line, however, seems to have an omission. It was:

=$A$1:INDEX($B:$B,COUNTA($A:$A))
but before the last parenthesis there should be a comma followed by the
last column number (a number relative to $A$1 in this example).

 

[Herbert Seidenberg]

To make the PivotTable ranges dynamic again,
you can restore the source data to a Table,
and the two differently named ranges will become dynamic.
Just don't touch the "Change Data Source" button again.