Allowing Filter in Protected sheet

[Rissky1]

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? 

[#2]

@dmartinez50 

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?

[#3 · Accepted 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...

[#4]

@dmartinez50 - YES!  Exactly what I wanted.  Thank you so much!

[#5]

@naveensrinivas21 

Hi, What do you mean by "Filter only first row?"

I know how to allow editing a range .. but not allowing users to select protected cells could solve the sorting-issue...

... but actually I want the users still to be able to select protected cells... because it's way more practical to copy data or navigate in the range.

So I don't know if your described solution here allows this... hence

... what do you mean by "Filter only first row"?

Thanks in advance.

[#6]

@Bernd_B yea, I didn't know what that meant either.  I did get the help I needed and its resolved.  What is it you are trying to do - maybe I can help?

[#7]

 @naveensrinivas21 Thanks. An approach that works. 

[#8]

Thank you. This is what I was looking for

[#9]

@dmartinez50   You have answered the question to the point. Well done. Thank you.

[#10]

just made sure the top row with filter included in the same condition as below cells (Locked/Unlocked) so looking columns is better with this issue and made sure i ticked

select unlocked cells

3. Sort

4. Auto filter

And work perfectly