Lock cell if value in another cell equals "Complete"

How do I lock a cell if the value of a cell if the value of another cell has the value "Agency" and unlock it if it doesn't?

Thus if B1 = Agency, C1 is unlocked and D1 is locked, and if other is selected C1 is locked and D1 is unlocked, and if B1 is null, so are C1 and D1? (and so on for B2/C2/D2?) The number of records entered (thus rows) can vary from 0 to several hundred.

I'd read that the code below would work if I put it in the Data Validation formula box for cell D1, but it did not. The cell was always unlocked and the the value in B1 displayed False when the condition was met. I couldn't put it in C1 because the Data Validation function is already in use for its pull down menu.

Please Login or Register  to view this content.

Here is what I am trying to do:

The excel spreadsheet resembles a hard copy of a data entry form that the user is entering the data into. One column in the form asks if the data recorder was employed by a government agency or other. If "government agency" is selected then C1 is unlocked and the user selects the agency from a pull down menu created using the Data Validation function. It "other" is selected, then C1 is locked and D1 is unlocked and the user enters the data recorders name. You cannot have a value in both C1 and D1, one must be null if the other one has a value. The only time they can both be null is if cells A1 and B1 are both not null (which indicates that there is actually a valid record being entered.)

Does anyone have an idea of how I might approach this issue? I have searched the internet for a solution for two days now and still cannot find an answer that works well for me. Please keep in mind that I am a beginner and to provide comments so I understand the whys.

A million thanks to all assistants in advance.

BTW: Please note Title is supposed to say "Agency" not "Complete". Tried to reedit post to change "Complete" in title to "Agency" but couldn't.

I think you may need to upload a sample excel file
Regards

Originally Posted by NewbieX

I'd read that the code below would work if I put it in the Data Validation formula box for cell D1, but it did not. The cell was always unlocked and the the value in B1 displayed False when the condition was met. I couldn't put it in C1 because the Data Validation function is already in use for its pull down menu.

Please Login or Register  to view this content.

To start with, this works fine. See attached file. Perhaps you made an error in the Data Validation settings.

Managing C1, though, is more difficult. One option you have is to use VBA to control this. I have a VBA solution that I will attach--but right now the forum is not rresponding.

Here you go