I have a spreadsheet that I manage on a company shared drive, and I need to protect a range. Other analysts need to access and modify certain cells, so is there a way I can only protect a single cell, or a range of cells without having a password prompt come up everytime someone wants to modify of of the unprotected cells?
The default setting for every cell is “Locked” which locks the cell when you “Protect” the worksheet. To change this setting for a range of cells: Select and then rich-click the range of cells that you want to unprotect. Click on “Format Cells”. Go to the “Protection” tab and un-check the box that says “Locked”. Then go to Tools -> Protection -> Protect Sheet. Users will now be unable to edit the cells that you have not un-checked the “Locked” box, but anything that you have done the above for they will still be able to edit.
This works well – thank you, Dwight. One more quick follow-up question: If I have a range that is autofiltered, can I just do the same thing to that column so that the dropdown menu still works?
cjones65 Wrote: ------------------------------------------------------- > This works well – thank you, Dwight. > > One more quick follow-up question: If I have a > range that is autofiltered, can I just do the same > thing to that column so that the dropdown menu > still works? That I do not know, you’ll just have to try it and see if it works. I think I recall having trouble locking lists/autofiltered ranges/dropdown menus, etc. The system for locking cells seems a little tempermental so you just have to play with it to figure out what works.
Cool. Thanks, Dwight.
Dwight Wrote: ------------------------------------------------------- > Users will now be unable to edit the cells that > you have not un-checked the “Locked” box, but > anything that you have done the above for they > will still be able to edit. Haha I just re-read this. Do you think there are enough double negatives in this sentence? 
you can lock a drop down list example: select empty cell go to Data -> Data Validation in Settings under Allow: select List under Source: type in different drop down values seperated by comma in Error Alert tab you can create custom message that a user will get if they try to type something into that cell
Dwight Wrote: ------------------------------------------------------- > cjones65 Wrote: > -------------------------------------------------- > ----- > > This works well – thank you, Dwight. > > > > One more quick follow-up question: If I have a > > range that is autofiltered, can I just do the > same > > thing to that column so that the dropdown menu > > still works? > > > That I do not know, you’ll just have to try it and > see if it works. > > I think I recall having trouble locking > lists/autofiltered ranges/dropdown menus, etc. > The system for locking cells seems a little > tempermental so you just have to play with it to > figure out what works. in the protect worksheet dialogue box, under the “allow all users…” area, make sure you select “Use AutoFilter”
^ Perfect. This works beautifully. Thank you, recycler et al.
recycler Wrote: ------------------------------------------------------- > in the protect worksheet dialogue box, under the > “allow all users…” area, make sure you select > “Use AutoFilter” That’s right.