Filtering in locked sheets

[Helen Wills]

Hi

We produce compliance templates for the rest of the business to use. The template itself is locked, with the exception of cells we are happy for team members to type in to.

Each team member has a row that includes
-some free text cells (unprotected)
- some formula cells (projected)
A mix of conditional formatting across the protected and unprotected cells

One location has asked if we can set the sheet up so they can filter the staff member rows (ie sort alphabetically)

However as the sheet is protected and I don’t want to unprotect certain cells in each row, what is the best way to do this?

(I’m not great with complex formulas etc, so step my step instructions appreciated)

Thanks

 

[mumps]

Manually unprotect the sheet. Click on any cell in row 1 and click the 'Filter' button in the 'Data' tab in the menu at the top. You should now see the down arrows in row 1 indicating that you can use the filter. Next protect the sheet and put a check mark in the box to the left of "Use Autofilter" and click 'OK'. Now the sheet is protected but you will still be able to filter the data.

 

[Helen Wills]

Hi

Followed the steps above and it worked to some extent i.e. before i could do nothing, and after, the the filter buttons appeared to allow basic filtering (the select/deselect drop down), but it didn't work when i wanted to sort A>Z / Z>A ? anything I am doing wrong?

 

[mumps]

If you want to sort, the cells in the column you want to sort have to be unlocked. When you protect the sheet, also put a check mark to the left of "Sort".

 

[Helen Wills]

Hi

this is still not working the way i need it to

I need to be able to sort data so that rows can be sorted alphabetically, whilst the columns in red MUST remain protected (bit also be able to be sorted)

Apologies if this is not making sense but thought a visual might help! please help!
[TABLE="class: grid, width: 1280"]
<tbody>[TR]
[TD="width: 64"]First Name[/TD]
[TD="width: 64"]Surname[/TD]
[TD="width: 64"]Position[/TD]
[TD="width: 64"]Contracted Hrs[/TD]
[TD="width: 64"]Current / Ex Team Member[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]Limit[/TD]
[TD="width: 64"]Acc No[/TD]
[TD="width: 64"]Limit[/TD]
[TD="width: 64"]Acc No[/TD]
[TD="width: 64"]Acc No[/TD]
[TD="width: 64"]Ex Team Member account deactivated?[/TD]
[TD="width: 64"] [/TD]
[TD="width: 128, colspan: 2"]Spent / Remaining [/TD]
[TD="width: 128, colspan: 2"]Spent / Remaining [/TD]
[TD="width: 64"]checked[/TD]
[TD="width: 64"]Last Date Checked[/TD]
[TD="width: 64"]Initials[/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 128, colspan: 2"][/TD]
[TD="width: 128, colspan: 2"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 128, colspan: 2"][/TD]
[TD="width: 128, colspan: 2"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

I don't think that will be possible. I believe that in order to sort the data in all the columns, all the columns must be unlocked. The only way I can think of to get around this, is to use a macro to do the sorting. The macro can unprotect the sheet, sort the data and then protect the sheet again leaving the columns in red protected. Will this approach work for you? Which column will you use for the sorting?

 

[Helen Wills]

yes it would. bit I have never built a macro before

I'd like the teams to be able to sort based on first name and surname

 

[mumps]

I can help you with a macro. It will prompt the user to enter the first name and last name and then it will sort the data accordingly. Did you want to protect the sheet with or without a password? I think that it would be easier to help and test possible solutions if I could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.