Excel filter issues

[Frank Natalia]

Hello,
I have a WS with autofilter in the headder. The WS contains 2 columns of formulas, the rest is supposed to be a Register filled up by unqualified personnel, so I have to protect the sheet for the safety of 2 columns in the middle.

The first columns is a list of family names and needs to be sorted A to Z everytime after new entries.

When I protect the sheet the Filter does not allow to sort from A - Z, it sends a message that I have to unprotect the sheet, even I ticked all options exept delete and insert rows and columns in Protect Sheet table. Other Filter options, like sort by text  or color, do work, but sort A to Z doesn't.

What's going wrong? Is there any solution? Thank you.

[#2]

Hi Natalia,

1) On unprotected sheet unlock the cells you'd like to allow to sort - select them, Ctrl+1 and

2)  Select proper options while protecting the sheet

[#3]

---

@Sergei Baklan wrote:

Hi Natalia,

1) On unprotected sheet unlock the cells you'd like to allow to sort - select them, Ctrl+1 and

2)  Select proper options while protecting the sheet

---

Thank you for answering. Unfortunately I started procisely from the actions you suggest. I unlocked the cells on the sheet, exept those with formulas. I ticked all options in the Protect Sheet table. Then I was most surprised with the result and called for help. Does it work on your computer? Is it just a bug on mine? Can I cure it somehow?

[#4]

Sorry, i was wrong, did'nt read carefully. For my knowledge - nope, you can't sort/filter the range with mix of locked and unlocked cells.

[#5]

Thank you Sergei. Sadly your answer is discouraging. Is there any other way to protect the formulas? I thought about Array but there is another problem I am going to post next. Any other way?

---

For my knowledge - nope, you can't sort/filter the range with mix of locked and unlocked cells.

---

[#6]

Please try this

- unlock all your range (with and without the formulas)

- for the cells with formulas set Hidden in the same menu

- protect the sheet and allow sorting/filtering

[#7]

Thank you for the suggestion. It did not help.

[#8]

Yes, that's only hide the formula.

One workaround could be as here https://www.exceltip.com/excel-security-protection/protecting-cells-containing-formulas-in-an-unprot..., it works on unprotected sheet.

The only minus with this aproach you can't protect the cell from deleting the formula (if someone press Del), but it prevents from any other editing.