Forum Discussion
Protect Sheet with Different User Permissions
Hello, I am working on a workbook with a lot of information and a lot of users. I have a primary worksheet that I want to lock down, say that all the data is in the range of A1:G100 of Sheet1. I ...
TylerB82 You won't be able to do this on the same sheet for both groups of users, because it's not possible to sort locked cells. The Microsoft help article mentions this - "Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting." (referring to the "Sort" setting)
There are 2 aspects to understand about this setting:
1. If the cells are unlocked (in the Protection tab of the Cell Format dialog), but you don't enable the Sort option in the Sheet Protection dialog when you protect the sheet, sorting won't work.
2. If the cells are locked, they can't be sorted, even if you enable the "Sort" option in the Sheet Protection dialog.
Lock or unlock specific areas of a protected worksheet - Microsoft Support
The way to work around this is to create 2 sheets. The first sheet should be as you have it currently, with Group A allowed to edit the range. Group B should not be able to make any changes.
On the 2nd sheet, you could have cells which are unlocked and allow the users in Group B to pick a column to sort, a sort direction, and a column to filter and a filter value. The filtering can be complicated if you need to be more flexible than just filtering by one value.
To show the data on the 2nd sheet, you would use a combination of the SORT(FILTER()) functions, referring to the data on the 1st sheet.
We have a few examples of how to set up the FILTER function on our blog - A to Z of Excel Functions: The FILTER Function | SumProduct.com
