determining whether two pivots share a cache

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
769
I have a huge document consisting of many pivot tables. I inherited it, and the person who created it is long gone. Part of my job is to scale down this document.

I wonder if all the pivots are using the same cache, or not. If not, I could re-create them as copies of a single pivot, thus having only one cache. But I'm actually not sure if that's necessary. Is there a way to tell?

Note that I'm talking about cache, not source data. They are all using the same source data for sure.

This is in Excel 2007.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I have a huge document consisting of many pivot tables. I inherited it, and the person who created it is long gone. Part of my job is to scale down this document.

I wonder if all the pivots are using the same cache, or not. If not, I could re-create them as copies of a single pivot, thus having only one cache. But I'm actually not sure if that's necessary. Is there a way to tell?

Note that I'm talking about cache, not source data. They are all using the same source data for sure.

This is in Excel 2007.
Try this macro to determine what cache is being used for each pivot table. Select a field inside of each and pivot table and then run this macro:

Rich (BB code):
Sub ViewCacheIndex()
Rich (BB code):
 On Error GoTo err_Handler 
 MsgBox "PivotCache: " & _     
    ActiveCell.PivotTable.CacheIndex      
Exit Sub 
 err_Handler: 
 MsgBox "Active cell is not in a pivot table"     
End Sub

Just thinking out loud but could some of the pivot tables be combined using grouping? I don't know how you slice your data but if the source data is all the same, I'm wondering if you could get by with using less pivot tables by combining more of the pertinent data in one pivot table...not that one would solve your problem but in terms of consolidating more of your data, using less that way.
 
Upvote 0
Thanks jakeman, I will try your macro on Monday.

Yes, there could probably be fewer pivot tables as well. The document as it exists is huge and complex, so I'm going to be taking a number of different approaches to hone it down until it becomes more usable.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top