I’ve never noticed the “Allow Users to Edit Ranges” option under Tools > Protection until Shane pointed it out the other day. This seems like it could be fairly useful if you have decent domain level groups established. One way I can see to use this is in cross-departmental collaboration. What? Man do I sound like an über-dork. Maybe an example is in order. Here’s a really complicated budget worksheet:

Now I only want certain people to be able to change certain numbers. Since I was so diligent setting up the users and groups in my domain, it’s fairly easy to assign a group to a cell. I’ve already assigned the revenue number to the gSales group, which means that anyone in the gSales group can modify that number, but anyone else will need a password. Now I want to restrict the COGS and Operations budgets.

This menu command brings up the following dialog. You can see that I’ve already set up a restriction on B3.

I click on the New button to create a restriction for a different range.

The operations folks will be responsible for the COGS number and their part of the overhead. I use the comma (union operator) to put both ranges in one, but I could just set them up separately.

The Permissions button brings up the domain users dialog. It’s empty for me, but I can choose Add and go through several more dialogs to get to the group I want. I don’t show all those steps here, but it’s not too difficult. I find my gProduction group and assign it to this range. Now anyone in that group can modify B5 or B11.

I’m in the production group, so I can go crazy on COGS, but if I try to change the revenue number, I get

You have to protect the sheet, but you don’t have to unlock these cells – apparently this process takes care of that for you. Now that I’ve got Sales and Production set up, I’ll add ranges so Marketing can change their budget and Accounting can change theirs.