Restricting access to a pivottable's source data.

[showboat098]

I'm working in Excel 2010. I have a pivot table that's accessible to my entire firm. The source data for the pivot table is stored within another worksheet file separate from the pivot table. I want to restrict other individuals within the firm from being able to access the source date without affecting the pivot table. Placing a password on the source data also password protects the pivot table, and I'm trying to avoid that. Any other methods I'm not covering?

 

[Jerry Sullivan]

Any data that is stored in the PivotCache is potentially accessible to a user of the file with the PivotTable.

If you limit the data stored in the PivotCache to non-restricted information then a user who doesn't have access to your source workbook will still have normal PivotTable functionality with the exception of not being able to refresh the data. This is similar to what happens if your Pivot's source data is a separate file on your computer's local drive and you email the Pivot's workbook to other users.

If the users need a means to refresh the Pivot to get updated data (in lieu of your distributing an updated file), you could use a Proxy file that you update with non-restricted data. Place that Proxy file in a shared location and have the Pivots use it as the data source. Then you update the Proxy file and let user's click the refresh button to pull in the updates.