Forum Discussion
Allowing Filter in Protected sheet
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?
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”
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
Are you able to attach your workbook so I can see the issue. BTW what version of Excel are you using?
- 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.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 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.
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.
