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 want to allow only a small portion of the users, call them Group A, to edit the contents of the primary worksheet. Adding rows, deleting rows, selecting cells and changing their values, working in the worksheet like there are no protections on it. Then I want the rest of the users, call them Group B, to be able to view the sheet and sort the data. Just sorting, not editing, adding, deleting, formatting, or anything other than sorting using filters. I've been struggling to come up with the right combination of Protect Sheet settings and Allow Edit Ranges and Permissions settings. If anyone could help with this, that would be great!
Summary
Group A: Edit cells, add/delete rows, do whatever they want with the worksheet
Group B: Only sort/use filters, nothing else
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
