Forum Discussion
Allowing Filter in Protected sheet
Rissky1 You're right, but I've got it! I tried unlocking your sheet, but the password didn't work. I used the below solution and it worked for me.
Step 1: Make cells editable so that sorting will work.
Add cells we want to sort by a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.
1.Select all the cells you would like the user to be able to sort, including their column headings.
2.Go to the Data tab and click Filter. An arrow should appear next to each column header.
3.Go to Review tab-> Allow Users to Edit Ranges 1.Click “New…”
2.Give the range a title.
3.“Refers to Cells” should already contain the cells you want to allow sorting on.
4.If you want to allow only certain people to sort, give the range a password.
5.Click “OK”
Step 2: Prevent users from editing these cells
When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.
1.In the “Allow Users to Edit Ranges” dialog: 1.Click “Protect Sheet…”
2.Give the worksheet a password
3.Uncheck the worksheet protection property called “Select Locked Cells”
4.Check the “Sort” property and the “AutoFilter” properties
5.Click “OK”
Source: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_o365b/sort-edit-range-protected-sheet-errors-in-excel/8d50d3a4-d25b-44c1-8168-c41bd87c4c57
Noman_Nayan I know you said this is resolved, and I don't see it otherwise mentioned in the chain above, but I had this same issue.
My resolution was this;
When selecting to protect your sheet, scroll down on the protection options then tick "sort" and "use AutoFilter". This did the job for me.
So I do have those two checked off but the problem is I still can't "sort" A-Z or Z-A when protected.
Can you?
Rissky1 You're right, but I've got it! I tried unlocking your sheet, but the password didn't work. I used the below solution and it worked for me.
Step 1: Make cells editable so that sorting will work.
Add cells we want to sort by a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.
1.Select all the cells you would like the user to be able to sort, including their column headings.
2.Go to the Data tab and click Filter. An arrow should appear next to each column header.
3.Go to Review tab-> Allow Users to Edit Ranges 1.Click “New…”
2.Give the range a title.
3.“Refers to Cells” should already contain the cells you want to allow sorting on.
4.If you want to allow only certain people to sort, give the range a password.
5.Click “OK”
Step 2: Prevent users from editing these cells
When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.
1.In the “Allow Users to Edit Ranges” dialog: 1.Click “Protect Sheet…”
2.Give the worksheet a password
3.Uncheck the worksheet protection property called “Select Locked Cells”
4.Check the “Sort” property and the “AutoFilter” properties
5.Click “OK”
Source: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_o365b/sort-edit-range-protected-sheet-errors-in-excel/8d50d3a4-d25b-44c1-8168-c41bd87c4c57
dmartinez50 You have answered the question to the point. Well done. Thank you.
dmartinez50 - YES! Exactly what I wanted. Thank you so much!
