Selective protection on a worksheet

[2davidc8]

How can I protect only certain formulas and conditional formatting in a worksheet while still allowing the cells to remain open for data input?
Review/Protect Sheet... or Home/Format/Protect Sheet... seem to be all-or-nothing.
Also, how can I make the protection apply only to certain cells but not to others? The Excel help info indicates that I can select a range for protection but I can't make it work. The whole procedure seems to be about protecting the entire sheet, not protecting a range or individual cells.
Appreciate your help!

 

[Joe4]

Protecting sheets is NOT an all-or-nothing approach. You can lock the cells you want protected, and unlock the ones that you want to allow to be edited.
See: Lock cells to protect them

 

[2davidc8]

Thanks for your reply. Perhaps I should rephrase my question.
I would like to protect the conditional formatting but still allow data to be entered into the cell. I find that protecting the conditional formatting also locks the cell.
I can't find a way around this ...

 

[Joe4]

You can certainly do that.
See here for exact instructions on how to do that: ExtendOffice - How to protect cell formatting but only allow data entry in Excel?

I tried following those instructions myself, and it worked. I was able to do data entry, but not edit Conditional Formatting.

 

[2davidc8]

Hi Joe4,

Thanks for the reference! We're almost there. When I tried those instructions, it worked well--just what I wanted--except for one thing:
I would like the user to be able to filter the data to view a subset of the data (two of the columns are dropdowns), but once the sheet is protected, this is not allowed. The user is able to sort, but not filter. What should I do?

Thanks!

 

[Joe4]

Hi Joe4,

Thanks for the reference! We're almost there. When I tried those instructions, it worked well--just what I wanted--except for one thing:
I would like the user to be able to filter the data to view a subset of the data (two of the columns are dropdowns), but once the sheet is protected, this is not allowed. The user is able to sort, but not filter. What should I do?

Thanks!

Did you try selecting the "Use Autofilter" option in the "Protect Sheet" box?

 

[2davidc8]

Yes, I did. Unfortunately, once the sheet is protected, all filtering options are still grayed out for the user.

 

[Joe4]

It seems to work for me.

Just like the article shows, you need to first "unlock" all the cells that your data resides in.
Then, check the "Use Autofilter" box when protecting the sheet, and you are able to then use the Autofilter.

 

[2davidc8]

Thanks for sticking with me.

Here's what I did:
1. As instructed, I first selected a range of cells, then I went to Home/Format/Format Cells ... and unlocked the cells in the range:

2. Next, I went to Review/Protect Sheet and selected:

and clicked OK.
3. But now in Home/Sort & Filter .., the Filter option is grayed out. Also, in the Data tab the Filter option is grayed out. And when I try to force the issue with the keyboard shortcut Ctrl-Shift-L, I get the error message:

What do you suppose I'm doing wrong?
Sorting works, and Conditional Formatting is grayed out, as I wanted. Just can't Filter.

 

[Joe4]

OK. It allows me to use pre-existing filters, but not to create new filters.
Can you set-up the filter on the range BEFORE protecting it?
Then they should be able to interact with the pre-existing filters.