IF/THEN function in Data Validation

Question

I am trying to create a custom data validation whereby a drop down list only becomes enabled if a date/text is entered into a previous cell. 

Any help will be appreciated. 

TIA

Answer 1

Hi Josh,

Good news! There is no need to use Macros.

I have tested use formula to achieve our goal and it works. For example:

I input formula into data validation of Cell B1: =If(A1<>"",$B$2:$B$6,$B$1) 

Then you can add data validation for A1 as date only so that if there is no date value in A1, B1 show nothing.

Regards,

Eric

Answer 2

Amazing - so simple. 

Thank you.

Answer 3

Thank you for your email.

I'm looking at the second option you gave but it doesn't seem to be

working, please could you assist.

What I want to achieve is that if a cell doesn't have a date entered the

next cell with a drop down to be disabled.

Many thanks

Answer 4

Hi Josh,

I have two idea.

1. Dependent drop-down list

By this way, you will have two drop down list (you can create third if needed), the second list can't be viewed unless you have selected an option in the first one. I know you want to enable a list based on value in a cell not another list, but this method is simple and efficent.

2. Lock a cell based on value of another cell

Honestly, Macros is more complex but useful, you can lock the cell with the list based on the value of any cell and there will be more condition you can apply on the list like (more than/less than).

If anyone has other solutions, welcome to share here.

Regards,

Eric