Adding Custom Data Validation when a cell contains a Drop Down List.

Hello all,

My problem - I would like to Lock a cell dependent on whether or not another cell has data in it. Previously mentioned cells already contain Data Validation (Drop Down List). Basically if you pick from this list, I don't want you to pick from the list within that cell. What I mean by "lock" is, I would like to prevent a user from entering data via a stop error message. First problem is the cells already utilize Data Validation. I thought my work around would be to reference that cell to another and apply Data Validation to that cell. When I try this in Custom Data Validation using (example, =cell="") it will not recognize the value in the cell because it is sourced from a list. I've attached an example problem to show what I am trying to do. For a visual I have also added Conditional formatting to color fill the cell if the other is being used. Essentially if the cell has color fill in it , then the user is also stopped from picking from the list in that cell. Thank you for any help.

Hi, welcome to the forum.
Maybe I missundertood your question/request, but data validation is just that, only valid data (from the drop-down list) is permitted, the error message shows that en the focus is set to the cell.
Nothing else can be entered.

Think I see got it. You only want to permit or List A or List B
To achieve this I think only VBA will solve it for you, you will have to add macros and code them accordingly

Well, now's the best time to start learning.
VBA is very simple and straightforward.
The problem is that of course we can offer a helping hand but in the end it's you that has to maintain it an understand it.
There a loads of free tutorials and the most efficient way is to follow these and start on your own from scratch.
We all started with recording a macro and then looking at the code and see what it did.
Then... imagination is the limit, all you need (plenty off) is time and much more of that.
Respecting the syntax rules it's like writing a story.
If I do this and the value of cell A is equals to this then cell b will have to be that else if this etc. etc . etc.

The you 'translate this story to VBA

Please Login or Register  to view this content.

I'll take a look at your file and see if I can put you on the right track but ... take the time to lear, it's fun and once you get the hang of it, it's addictive

This is a little more than basic VBA but for starters.
First, please avoid Merged cells like you did, that's a P in the A when it comes to selecting cells using VBA.
I reduced it to a single cells for now.
To make it work cells have to be locked or unlocked and the only way locked and Unlocked works for selections is ia a worksheet is protected.
I protected the worksheet (no password) but only unlocked cells can be selected.
List A and List B are unlocked, so you can select an item from either dropdown list, if you select A then B is locked (and black fill like you had)
Clear the contents in the one selected and both are free to access again.
I have no idea what you want to achieve with this but hope it gives you an idea how it works and, once again, VBA is not that complicated but there are some things that exceed the beginner's steps

For starters, a link to check out, has nice samples and explains things a little
http://www.cpearson.com/Excel/vbe.aspx

You're welcome, I'm convinced that once you start you'll love it.
It's a great tool to enhance the use of Excel