Granular Excel Pivot Table Security

1
votes

I currently produce a report for 5 managers that contains data for each of their employees. The raw data lives in a table in Excel file A, while Excel file B has a pivot table on it connected to the data in file A. I use the manager name as the report filter to list all of their employees data in the pivot table for that manager.

What i am trying to do is prevent the managers from being able to use the report filter to view other manager employee's data. I know I can do this easily by locking the workbook, thereby preventing the manager from using the pivot table at all... the problem arises because I would like the manager to have access to a Slicer to filter by employee name (a row label). If I lock the workbook then the Slicer is not useable, and if I enable "Use PivotTable Reports" then they can access the report filter.

Ideally what I would like is some way to force the data connection between file A and B to call for an update from file A when the report filter is changed (but not the Slicer). Because file A resides only on my PC, the update would fail in the manager's copy of file B.

Does this make any sense? I want to disable the use of the report filter, but still allow the Slicer to filter based on row label...

1
excelpivot-table
Before we get into an XY problem, are you sure you can't use the slicer when locked? Look at this thread or this search, does nothing there help? – BruceWayne
I had looked at that before posting, yes - the problem seems to be that protecting a sheet but allowing pivot-table updates is all or nothing - either all filters can be used (report filter or slicer) when "use pivot-table reports" is checked, or none can be. – mattinwpg
since the data for each manager's file is connected to file A, can you just set up a filter in the connection to only show the employees data for that manager. (I know that means modifying each manager's file separately, but it would work). – Scott Holtzman
Hey Scott - not sure I know enough about data connections to set up a filter on it? I set this one up in file B by going Insert - Pivot-table - Use an External Data Source, and then navigating to file A. Unless your suggestion is to set up separate tabs in file A for each manager... but i would like to not change or edit the source data if at all possible. – mattinwpg

1 Answers

1
votes

A functional answer turned out to be fairly easy... I hid the row that included the Report Filter, protected the sheet but allowed pivot table changes, but disallowed most everything else.

So, while the report filter IS still there, it's in a hidden row that can never be unhidden by the end user. The Slicer still allows row label filtering.