Forum Discussion

Frank Natalia's avatar
Frank Natalia
Copper Contributor
Sep 12, 2017

Excel filter issues

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...
excel
Formulas and Functions
Need Help
Frank Natalia's avatar
Frank Natalia
Copper Contributor
Sep 13, 2017
SergeiBaklan 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?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 14, 2017

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.

  • Frank Natalia's avatar
    Frank Natalia
    Copper Contributor
    Sep 14, 2017

    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.

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 14, 2017

      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

      • Frank Natalia's avatar
        Frank Natalia
        Copper Contributor
        Sep 14, 2017

        Thank you for the suggestion. It did not help.

Resources