Archive pivot tables

opislak

Board Regular
Joined
Feb 28, 2017
Messages
68
Hi all,

Is there a way to prevent users from refreshing pivot data?

I have pivot tables looking up data through ODataFeed. After 2 years, the original data is being erased from the servers, but we have to keep all the pivot tables (managers do ask us to compare data from -say- 5 years ago with today's data). I now keep the pivot tables and put a Warning on top, asking the users NOT to refresh the data (for if they do, the table is cleared of course). I store an original copy of the Excel files in case someone would still erase the data by refreshing. But that doesn't seem like a neath thing to do.

Any advise is welcome. Thanks for your replies.
Patrick.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
The best way is (manually or with VBA) copy the whole pivottable and paste it special as values and add good comments.
Of course you loose the ability for showdetails etc.

But if you also keep the data over those +5 years, and those data from 5 years aren't modified, with some slicers you can create the old pivottable again. You can you which year or years, you want the data ...
 
Solution

opislak

Board Regular
Joined
Feb 28, 2017
Messages
68
The best way is (manually or with VBA) copy the whole pivottable and paste it special as values and add good comments.
Of course you loose the ability for showdetails etc.

But if you also keep the data over those +5 years, and those data from 5 years aren't modified, with some slicers you can create the old pivottable again. You can you which year or years, you want the data ...
Thanks. Works fine.
 

Forum statistics

Threads
1,175,458
Messages
5,897,549
Members
434,662
Latest member
adamdom

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
Top