Pivot Tables with different groupings based on same data table

  • Thread starter Thread starter billwoodard
  • Start date Start date
B

billwoodard

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? I want to have only one master data table to which new data
can be added and not run the risk of failing to update many separate tables.
 
billwoodard said:
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? I want to have only one master data table to which new data
can be added and not run the risk of failing to update many separate tables.
Click to expand...

I didn't test this but one way might be to create each pivot table in a
new worksheet.

gls858
 
Roger Govier said:
Hi Bill

It happens because both PT's are using the same Pivot Cache.
To get around the problem.
First convert the table back to a list using the Insert>Design>Convert to
List
Create two separate Named ranges, both pointing at the same set of data
using Formula>Name Manager
Call one Name Weeks, the Other Months
Create your 2 PT's giving one the Source=Weeks, the other =Months
Do your Grouping on each and they will be treated separately

If the data set is going to grow, you can make both of the named ranges
Dynamic by using the following formula in the Refers to box of Name Manager
=$A$1:INDEX($B:$B,COUNTA($A:$A))
Click to expand...
Doesn't your dynamic update formula need a column number at the end, e.g.
=$A$1:INDEX($B:$B,COUNTA($A:$A),2)
Your suggestion worked very nicely. Thanks a lot.
 
Back
Top