Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Tables with different groupings based on same data table
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Tables with different groupings based on same data table
billwoodard wrote:
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. I didn't test this but one way might be to create each pivot table in a new worksheet. gls858 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Tables with different groupings based on same data table
"Roger Govier" wrote: 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 InsertDesignConvert to List Create two separate Named ranges, both pointing at the same set of data using FormulaName 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)) -- Regards Roger Govier "billwoodard" wrote in message ... 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables with different groupings based on same data table | Excel Discussion (Misc queries) | |||
Distributing Pivot Tables where pivot table data can't be accessed | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
pivot table groupings | Excel Discussion (Misc queries) |