Protection That Allows Sorting

[kskapin]

Hello!

I have a sheet that contains various columns that I would like to protect or hide from the standard editing/ viewing. There are multiple user groups who access this sheet, and I finally got all the information correct in a column, so I don't want any changes to it being made.

I've done the standard "Unlock all cells, lock the ones you want to protect, then protect the sheet" procedure, ensuring that "Sort" and "Use AutoFilter" are applied. However, once I protect the sheet and go to sort, I get a message that says "The cell or chart you are trying to change is protected and therfore read-only."

Anyway to work around this with VBA or different settings?

 

[cyrilbrd]

Hi kskapin,

If you:

Create a Filter on Header Row.

1-selected the cells to be locked, then Format / Cells / Protection and Ticked "Locked".
2- Tools / Protection / Protect Sheet and select "select unlocked cells", "sort" and "filter", the choose password of your choice, enter twice and click OK.

then even if the cell where the filter is placed couldn't be selected, you could still click on the filter, apply the selection of your choice, and all would be filtered accordingly (locked, unlocked).

 

[SKKS1976]

I saw this post and it was very helpful. However, Excel does not seem to be very consistent when applying the rules listed above. For example, when I followed steps 1 and 2 (I selecting cells on a column of a Table to be locked, not including the Header), it worked beautifully. I was able to sort on any column in the Table but not select the locked/protected cells.

I then proceeded to lock/protect another column. Again, beautiful. Then, I proceeded to lock/protect a third column in the table (the Table has 5 columns) and then Excel would not let me sort at all.

I then started from scratch - I unlocked the entire worksheet to determine if Excel has a limitation on what type of data it allows locking and then sorting. Some of my columns of data in the Table have formulas, some contain just data. I could not figure it out. And now I am to a point where I cannot repeat any part of the process. Using the SAME table, I cannot lock/protect a single column and then sort on it. It is as if I've confused the software (or could just be user error, of course).

What am I doing wrong?

What exactly do you mean by "Create a Filter on the Header Row" ?

Thanks ahead of time for help.

 

[SKKS1976]

By the way I do not know VBA.

 

[cyrilbrd]

...
What exactly do you mean by "Create a Filter on the Header Row" ?
...

In the event of a single set of data, Creating a filter on the header row would allow for sorting by simply selecting Data / Sort & Filter / Filter.