Sorting a table in a protected sheet when some cells are locked

Teapotlid

New Member
Joined
Jan 30, 2015
Messages
14
I have a form which people will use to enter data in a standardized way.
The table has columns A:C as formulae and columns D:G as empty cells ready for people to add the necessary data using a mix of Data Validated pick lists or free-form text.
I've locked columns A:C to prevent people accidentally overwriting the formulae. Columns D:G are unlocked.
I've then protected the sheet.

Sometimes people want to sort the table to put it into a more sensible order for them to eyeball the data before they send it into me.
Even if I allow sorting when I set up the protection, it seems like a table with a mix of locked & unlocked cells can't be sorted.
I could pop a button on the sheet attached to a macro that unprotects, sorts then reprotects but is there anything easier that could be done?

I'd love to know if there is something nifty out there that I'm just not aware of.
Many thanks in advance
Teapotlid
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

RatExcel

Board Regular
Joined
Aug 24, 2014
Messages
222
That's one of Excel disfunctionalities that should be fix long time ago but it is not. Even if you check Sort and Use Autofilter options when protecting the sheet you won't be able to sort. You can only filter the values. Sorting is contected with changing the sequence of rows which basicly is changing the cells. Maybe it's possible in Excel 2013 but for sure not in 2010.
 

Teapotlid

New Member
Joined
Jan 30, 2015
Messages
14
Dang!
Thanks Rat. I guess I'll just have to record a macro & pop a button on my form.
Not the end of the world but it does seem a bit inelegant.
 

PJExcel

New Member
Joined
Apr 21, 2016
Messages
2
Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

1.Select all the cells you would like the user to be able to sort, including their column headings.
2.Go to the Data tab and click Filter. An arrow should appear next to each column header.
3.Go to Review tab-> Allow Users to Edit Ranges 1.Click “New…”
2.Give the range a title.
3.“Refers to Cells” should already contain the cells you want to allowing sorting on.
4.If you want to allow only certain people to sort, give the range a password.
5.Click “OK”


Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

1.In the “Allow Users to Edit Ranges” dialog: 1.Click “Protect Sheet…”
2.Give the worksheet a password
3.Uncheck the worksheet protection property called “Select Locked Cells”
4.Check the “Sort” property and the “AutoFilter” properties
5.Click “OK”


This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.
 

PaulusPigus

New Member
Joined
May 28, 2019
Messages
1
I have tried this solution many times and can not get it work. Although the filtering works, the data range cells can be deleted (e.g. deleted) by the user. Any suggestions?
 

rmb4466

New Member
Joined
Feb 1, 2010
Messages
11
Agreed. The solution posted by PJExcel essentially leaves the whole table unprotected.

I'm in the same situation as the OP: some columns using formulas that I don't want users to inadvertently edit, some data-validated columns, and some free-entry columns. I'd like my users to be able to sort and filter but still be disallowed from editing the columns with formulas. So far, the only solution I've found is the macro solution noted accurately as "inelegant" by the OP. Would love to learn of another solution.
 

rmb4466

New Member
Joined
Feb 1, 2010
Messages
11
Figured it out. Perhaps the solution posted by PJExcel is accurate afterall, just a bit unclear. Here's what worked for me:

- Ensure the cells you want users not to be able to edit are locked. [This is the part that I screwed up. "Step 1" made it sound to me like all cells should be unlocked.)
- Ensure the cells you want users to be able to edit are unlocked.

- Select the entire range you want users to be able to sort (including headers).
- Use Review>Allow Edit Ranges to name this selected range.

- Protect the sheet
- "Select Locked Cells" _not_ checked
- "Select Unlocked Cells" checked
- "Sort" checked
- "Use AutoFilter" checked

With this method, users won't be able to select the locked cells (without unprotecting), so won't be able to edit them. Could be a problem if users need to copy data out of the spreadsheet, but it works for my purposes.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,900
Messages
5,471,397
Members
406,759
Latest member
jackflint

This Week's Hot Topics

Top