Cannot change one pivot table without affecting the others

USAMax

Well-known Member
Joined
May 31, 2006
Messages
825
My data is not that large, about 1000 rows but I want to look at it in different ways.

One pivot tables shows the average auction price for each hour.

One pivot tables shows the average auction price for each month.

I have several more but you get the idea. The problem is that when I group one by month the other one changes and when I change the other back it affects the other. I want to have several tables on the same sheet. I tried to click the box, "Defer Layout Update" but that did not help.

Can someone help me?
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
825
The data is within the same workbook so I don't think there is a way to unshare it except to make the data static, but I want to keep adding data to it and have it update the table.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,319
The data is within the same workbook so I don't think there is a way to unshare it except to make the data static, but I want to keep adding data to it and have it update the table.
With data within the same workbook I tried
..temporarily redefining the data range to force Excel to unshare the data cache.
and it worked for me

M.
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
825
Thank you Marcello but I think it is best if I just share the workbook.

https://www.cubby.com/pl/My+Cubby/_dbf9a6ec9a7e467b951e76e7a342fad0

On Sheet2 and Sheet4 there are three Pivot Tables. You will see that Grouping one of the Pivot Table's, "Auction Date" by hours or months changes all of the Pivot Tables. I also need a way to group these by the Day-Of-The-Week.

I cannot believe I am the only one with this problem.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,319
You can recreate the Pivot Tables following these steps
(see the link I provided in #2)


Create a PivotTable report that is based on the same cell range as another report without sharing the data cache
1.Ensure that there is an existing PivotTable report that is based on the same range that you want to use for the new PivotTable report.
2.Click any blank cell in the worksheet outside the PivotTable report.
3.To start the PivotTable and PivotChart Wizard, press ALT+D+P.


------------------------------------------------------------------------------------------
Tip

To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:
1.Click the arrow next to the toolbar, and then click More Commands.
2.Under Choose commands from, select All Commands.
3.In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

--------------------------------------------------------------------------------

4.On the Step 1 page of the wizard, click Microsoft Office Excel list or database, and then click Next.
5.On the Step 2 page of the wizard, select the range of data on which you want to base the new PivotTable report, and then click Next.
6.When the PivotTable and PivotChart Wizard displays a message asking if you want to share the data cache, click No.
7.On the Step 3 page of the wizard, select a location for the new PivotTable report, and then click Finish.

Hope this helps

M.
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
825
Thank you Marcelo, that did the trick and I have added the shortcut to the Ribbon.

Microsoft should offer a check box that says, "Do you want to share the data cache?"

A truly detailed and thorough answer, thank you!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,319
Thank you Marcelo, that did the trick and I have added the shortcut to the Ribbon.

Microsoft should offer a check box that says, "Do you want to share the data cache?"

A truly detailed and thorough answer, thank you!
Great that it worked!

You are welcome and thanks for the feedback.

M.
 

Forum statistics

Threads
1,082,638
Messages
5,366,694
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top