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
peteryac60 yes - I have that box checked too
Are you able to attach your workbook so I can see the issue. BTW what version of Excel are you using?
peteryac60 I am attaching a truncated version of the s/sheet. Password is the same word as in column B - Randel.
Using drop down filter, I try to sort and get the error message. Like sorting column F in A-C.
Using Excel under Microsoft Office 365 ProPlus V 16.0.11328.20392
Thanks - I understand the problem now.
I think because the cells are locked Excel won't allow you to sort.
The only way I can think to overcome this is to write some VBA code and have a button on your spreadsheet which the use can activate. The code will unlock & unprotect the sheet, sort it and then reapply the protection.
Will you allow you user to sort only on one column i.e. street address? Or are you going to allow them to sort on any column. If the latter this makes the VBA code harder.
peteryac60 So it sounds like Excel will not allow sorting when the range includes locked cells? Very strange given sorting does no actual change individual cells but just puts them in a different order, and considering the actual protect sheet command has a box to allow filters.
The reason certain cells are locked is because there are about 20 different users in different countries (the final s/sheet has about 1,000 rows) and, last year, they kept changing things based on local practices so it made the individual sheets a nightmare to combine. So I was trying to create a goof proof version this year. Then was asked about filtering because some of the individual s/sheets being done were actually created using sub-sets of prior sheets and the user wanted to filter to identify duplicates or differences. There are additional columns I didn’t include in the truncated version I posted here that include sales or property values or payroll and, for certain columns, hidden columns, conditional formatting and/or data validation - hence all the protection. In theory, a user might want to sort by any of those columns to rank locations by size in a specific category or for some other reason.
Not sure how creating a VRB code would work. Unprotecting the s/sheet even for a short time exposes it to intentional or unintentional modifications and makes the consolidation of the various separate s/sheets into the template problematic.
So there may not be an easy way to make this happen and, in my opinion, this issue is an unnecessary flaw in Excel.
Rissky1I copied all your data into a new spreadsheet and protected it with the allow autofilter and sort boxes ticked.
It would not sort until I reformatted ALL the cells in the drop down filter, including the colored header row (which had some locked cells) to remove the ticks from the locked and hidden boxes.
Then it worked.
GeraldGreeenhough19 - when you say “reformatted” all the cells, what did you change? Are you talking about all the cells in the spreadsheet?