Using Drop Down With Sheet Locked

August 30, 2009 at 10:12:35
Specs: Windows XP

Hello, I have sheet that i am using to pull some data form other sheet using VLookup. However, I would like to be able to use the drop down function with sheet protected. As it stand i have to unprotect the sheet to able to use drop down function.

please help

Tanks in Advance for your help.


See More: Using Drop Down With Sheet Locked

Report •


#1
August 30, 2009 at 17:49:03

Why don't you unlock the cell with the drop down before you protect the sheet?

Format...Cells...Protection tab...uncheck the Locked option for that cell only.

The users won't be able to change the Data Validation criteria, but they will be able to chose from the drop down list.


Report •

#2
August 30, 2009 at 17:53:09

BTW...Your subject line says Using Drop Down With Sheet Locked.

You don't lock a sheet. You protect it.

Locked only refers to individual cells within a worksheet and locking/unlocking a cell has no effect unless the sheet is protected.


Report •

#3
September 2, 2009 at 21:05:15

Thanks for your response DerbyDad03,

I have tried unlocking the cell (Format...Cells...Protection tab...uncheck) before protecting the sheet. Yet, when I try using drop down I get "The cell or chart you are trying to change is protected and therefor read-only".

If it helps here is what I am trying to do.
I have a sheet with value in A1 thru A10
that I want people to choose from using drop down,
it works perfect until I protect the sheet.

Once again Thanks for your quick response.


Report •

Related Solutions

#4
September 3, 2009 at 04:53:42

re: I have a sheet with value in A1 thru A10 that I want people to choose from using drop down

Obviously, I can't see your spreadsheet from here, so I can't tell you what the problem is.

I assume you have selected a cell outside of A1:A10. You then used Data...Validation...Allow: List...Source: =$A$1:$A$10

Then:

Select the cell with the drop down. Format...Cell...Protection tab...uncheck the Locked option.

Tools...Protection...Protect sheet. Make sure that the Select Unlocked cells option is checked.

If you unlock the cell that contains the drop down and then protect the sheet while allowing users to select Unlocked cells, users should be able to choose a value from the drop down. I do it all the time...I just proved it to myself again this morning.


Report •


Ask Question