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!

 

[2davidc8]

Yes, that would be a partial solution. But the thing is, on an unprotected spreadsheet, you can click Data/Filter and all column headers become dropdowns, allowing you to do all kinds of filtering to your heart's content. But now I would have to come up with a few filters that I think the users might want and leave it at that.

It would be ideal to be able to protect any element of a spreadsheet (a table here, a formula there, a grand total cell, any conditional formatting rule, etc.) while leaving the rest of the worksheet open for data input. One could only dream ...
Or maybe resort to VBA?

Thanks for your help.

 

[Jaafar Tribak]

If you right click within the table\range to be filtered, the apply filter is not grayed out in the cell context menu.
You will still have to allow autofilter when protecting the sheet.

edit:
The clear filter menu is still grayed out in the cell context menu. If this is an issue, you should be able to use vba to add a custom entry to the cell context menu to apply\clear the filtering.

 

[2davidc8]

Hi Jaafar,

That works! That restores the full Filter functionality! Thank you!
The solution to my question was actually a combination of Joe4's and your added comment just now. Unfortunately, the forum software allows me to mark only one reply as solution.
I hereby express my gratitude for your contribution!

David

 

[Joe4]

You are welcome.
Glad we were able to help!