Worksheet Protection

[Stever1960]

I have a worksheet that I want others to be able to view and sort but not have the ability to change. When I protect the worksheet they can sort but still have the ability to change cells. When I protect any of the cells within the range they lose the ability to sort. Can this be done simply?

[Aardigspook]

Hi, welcome to the forum.

This is one of those annoying things that should be easy but isn't. When you protect the sheet, there is a box you can tick to allow users to sort - or so you would think. Unfortunately, Excel doesn't actually sort by re-arranging the rows - instead it actually changes all the values in the rows so that they're in order. The end result looks the same to the user, but as the values are being changed, if the cell is unlocked (which is what you need to prevent changes), then sorting doesn't work.

Luckily, there's a workaround - it seems a bit complicated at first, but is actually quite simple once you've done it a couple of times. It uses 'Allow Users to Edit Ranges', 'Auto-filter' and 'Protect Sheet'. The first enables you to set a range (or ranges) which users can modify even if the cells are locked (in other words, the cells act as unlocked, even though they're not); the second sets up columns to enable sorting; and the third, obviously I think, protects the sheet to stop changes being made apart from sorting/filtering.

Enough of the boring background - here's how to do it:

Part A - setting up the ranges users are allowed to sort.

1. Select all the cells you would like the users to be able to sort, including the column headings (that's important).
2. On the 'Data' tab, click 'Filter'. You'll see a little arrow appear next to each heading.
3. On the 'Review' tab, click 'Allow Users to Edit Ranges'.
4. Click 'New' and give the range a title (maybe 'Sort_Range_for_team1').
5. The 'Refers to Cells' box should already be filled in with the range you selected at '1' above.
6. [Optional] Click 'Permissions', then click 'Add' on the dialogue box which pops up. Enter the names of the users you want to be able to sort. You can do this directly (e.g. type 'Jim' then click 'Check Names'), or you can click 'Advanced' and find users that way (entering nothing in the Advanced search fields and clicking 'Find Now' will list all users and groups). I can't give you exact instructions on which users/groups to add as that depends on how your network is set up. Click OK. If you now click each user/group for which you've added permissions, you'll see that 'Allow' is ticked for 'Edit range without a password', which is what you want. Click OK, OK to get back to the 'Allow Users to Edit Ranges' dialogue box.
7. Click OK to get back to the worksheet.

(Note - if you want different teams/your boss/etc to be able to sort different ranges, you can repeat the above for different ranges - though I've never bothered with that myself)

Part B - prevent editing of the sort-able ranges.

1. Still on the 'Review' tab, click 'Protect Sheet'.
2. Un-tick 'Select Locked Cells' (i.e. the box should be empty).
3. Tick 'Sort' and 'Autofilter'.
4. Add a password.
5. Click OK. Re-type the password from '4'.
6. Click OK.
(Warning - make sure you don't forget the password, or you won't be able to do any amendments to the sheet again.)

Now, users can use the little arrows on the Auto-filter arrows on the column headers to sort. The 'Sort' buttons on the 'Data' tab are also enabled, allowing multi-layer sorts (sort by 'Last Name' then 'First Name' for example).

I hope that's clear enough and does what you want.

ps I found these instructions in another post somewhere some time ago, so can't take full credit for them; I've mislaid the link, but if I find it I'll edit this post with it, as it had a fuller explanation and a macro solution as well, I think.
Edit: found it: http://blog.softartisans.com/2013/10...ed-worksheets/

[Stever1960]

Thank you so much! This worked. I did however have to exclude the headings from my sort range. When they were included the headings were sorted with the data. It appears to work ok without their inclusion in the range. I did have a blank row between the headings and the data which was included in the sort range.

In any case, Thanks.

[Aardigspook]

You're welcome. Maybe the headings thing is something different in 2013 from 2010, as it only worked for me when I did include the headings. Glad to have been of help.

Now that you've got it working, please keep the moderators happy by marking this thread as solved (look above your first post for 'Thread Tools' and mark it 'Solved'). Thanks.

[Stever1960]

Actually I made one more modification. Since I had frozen panes at the top, when I scrolled down in the worksheet the filter boxes disappeared from view. To fix this I included the blank row in my frozen pane and still included it in my range of data.

I was looking for a way to indicate "problem solved" thanks for giving me the answer to that as well.