Lock cells based on response from another - give drop down list

**DHFE** · 01-09-2014, 11:43 AM

I've got a spreadsheet where if -A1 = Y- I want B1 to have a drop down list
If -A1 = N- I want B1 to be locked

So far i've got data validation that will lock and unlock B1, but when it's unlocked it's just a text entry box and i don't want that. I want specific entry choices.

1. Select B1
2. Click on Data > Validation
3. Allow: Custom
4. Formula: =$A$1="Y"

Can this be done? I can think of a way i can get a similar result with conditional formatting, but locking the cell would be better because the people who use the spreadsheet aren't the most competent and having the cell locked will prevent more mistakes than simply changing the colour

**4everGS** · 01-09-2014, 02:26 PM

I think you need a Macro to do that. Perhaps, you should seek help in http://www.excelforum.com/excel-programming-vba-macros/

**DHFE** · 01-10-2014, 05:39 AM

After a lot of searching and Trial and Error, i think i've worked it out. Rather than selecting custom, i select list in B1 and input a formula where if -A1 = Y- it gives one set of results to choose from in B1, but if -A1 = N- then you get a different set of results to chose from (i.e. none), then set up an error alert if they try and input anything into those cells not available in the drop down lists.

Edit
Ok, for some reason the error's stopped working now. It was working initially

Edit2
Looks like i've got it working the way i initially wanted.

Validation in B1
=IF($A$1="N",FALSE,LIST) - list being the list of options i want available (which has been set up on a different tab).
If A1=Y then B1 will have the list, but if A1=N then there's no options available. I can then add the Stop Error Alert (with the Warning and Information alerts the value can be left in the cell even when incorrect).

NOTE: A1 must be set to 'Y' to edit the data validation in B1 or you will receive an error when trying to accept any changes you make;
The list source must be a delimited list, or a reference to single row or column