SOLVED

Allowing Filter in Protected sheet

Copper Contributor

I have a worksheet that is password protected that is used by clients to input location data.  Before password protecting I enabled filters at the top row so there's the arrow to use to sort by whichever column needed.  When password protecting I check off the filter and sort boxes.  But when the client tries to filter they get a message that the worksheet is protected and they can't filter.  Isn't there a way to allow the use of filter in a protected sheet? 

28 Replies

@Rissky1 

 

Have you also checked the first option - select locked cells? 

this should make a difference.

 

thanks

 

Peter

@peteryac60 yes - I have that box checked too

@Rissky1 

 

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

@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?  

@Rissky1 

 

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. 

@Rissky1 

 

ok - no problem

 

By the way - even having a password on a protected sheet does not really protect it. Just google how to remove a PW and you will find it is quite easy!!

 

maybe not what you want to hear!

 

Peter

@peteryac60 

 

Understood.  Of course if these users were that smart I could trust them to not screw up the spreadsheet in the first place.  Counting on their lack of knowledge and inability to understand or execute VB routines to unlock the password :) 

 

Thanks for your help.

@Rissky1 

 

Another option for you to consider.

 

Would it help if you created one or more pivot tables for your users. You would start by converting you data into a Table (using Format as Table or CNTRL + T).  You could then create pivot tables (maybe with a country filter?) and other views of the data.

 

Having the data formatted as a table will also mean that you can easily add new records and refresh the pivots quickly.

 

I am not sure if this will be of any use to you - but just something else for you to think about!

 

good luck!

 

Peter 

 

I don't know if this helps.

Let's say you have 10 columns.

1. Select Ctrl+A and format cells -> Protection -> Uncheck Locked
- Filter only first row
2. Select the cells that you need to be protected from editing. And then format cells -> Protection -> Check Locked
3. Select the first row -> Go to Review -> Allow edit in ranges -> ok -> Protect Sheet -> check 1. select locked cells 2. select unlocked cells 3. Sort 4. Auto filter

This should help u filter and sort. but also edit if needed.

I hope i have helped u.

@naveensrinivas21 

 

I have made progress since the original post.  Users can now sort for specifics - like sort to find all records of one state.  What they can't do is sort by alpha or numerical.  They can't use filter to put the date in A-Z or low to high or anything that reorders the entire s/sheet.

 

Attached is an example of what I mean.  It's protected - password is the same word as in column B - Randel.

@Rissky1 the cells are locked. please unlock, then u will be able to sort it. 

@naveensrinivas21 Well the cells are obviously locked for a reason.  The point is to not let users change individual cells and corrupt the data.  Sorting doesn't do that so I want to permit sorting but not allow cell changes on locked cells.  

@Rissky1 

 

A clarification: there is actually a point to not allowing wholesale sorting - there are instances where formulas (and functions) use specific cell locations, and sorting changes what data is in those locations, invalidating the formula results.  Filtering does not change what data is where, just what is displayed, therefore it doesn't change the locations to which the formulas are pointed. 

@Lance31359  - Thanks for your input.  May have something to do with realtive vs absolute references because, if I unprotects the sheet/tab which has a number of formulas and references, I can still sort and the formulas still operate as intended.  Would be nice if Microsoft at least gave the option and let us decide.

@naveensrinivas21 Thank u so much.. yes its working..

@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.

1 best response

Accepted Solutions
best response confirmed by Rissky1 (Copper Contributor)
Solution

@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-rang...

View solution in original post