Filtering in locked sheets

Helen Malone

New Member
Joined
Nov 14, 2014
Messages
20
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,983
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.
 
Last edited:

Helen Malone

New Member
Joined
Nov 14, 2014
Messages
20
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,983
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 Malone

New Member
Joined
Nov 14, 2014
Messages
20
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!
First NameSurnamePositionContracted HrsCurrent / Ex Team Member LimitAcc NoLimitAcc NoAcc NoEx Team Member account deactivated? Spent / Remaining Spent / Remaining checkedLast Date CheckedInitials

<tbody>
</tbody>
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,983
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 Malone

New Member
Joined
Nov 14, 2014
Messages
20
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,983
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,284
Messages
5,467,741
Members
406,550
Latest member
miraclewhip

This Week's Hot Topics

Top