Data validation that only allows data entry based on specific criteria?

Hi all,

I'm wondering if someone can help. I'm trying to set up a data validation that will only allow a user to input a value in a cell, if another cell meets certain criteria.

In my example, I have a list of items in column A, column B asks for how many millilitre the item holds, however, because only certain items could possibly contain liquids, I want the user to only be allowed to put in a number if the item is one which is on my "list" tab (therefore, where I've highligted a cell in red they shouldn't be able to put in a value and if it's green they should be able to).

I was also hoping to apply some conditional formatting using the same logic as above, whereby the cell (in column B) would turn red if it's an item not on the list and green if it's an item on the list...but I can't seem to figure that out either.

Does anyone have any ideas? I've tried googling this but I just can't quite figure out the formula I need to use.

Many thanks in advance !

Hi davesexcel , thank you so much for this, this is exactly what I needed !

I applied this formula to the conditional formatting as well (without the >0 at the end) and it now highlights the cell to be filled in if the item is in the list in green.

But is there a way to do the reverse i.e. if the item is NOT on the list, for example I could set the cell to turn to grey? I'm not sure how you set a formula to say "if this is NOT is this list".

Thank you again !

Actually never mind, I found a way to do the above, I used the same formula you had, but putting =0 at the end did the trick

Thanks again for your help!