CODEWITHSUNDEEP
excelexcel-formula
Josh Wright
Josh Wright

Reputation: 13

Issues with using "IF" statement and empty cells in Data Validation

I have two drop down menus where the first one determines what options the second one shows. I wish to make it so that if the first drop down menu has nothing selected, you cannot type anything into the second.

I am working on an interactive form to be used at work where there is a drop down menu (A1) for product family, and then the selection there drives a second drop down menu (A2) that displays products in that given family.

For this, we used three tables (with mock data): ProductFamily (Fruits & Vegetables) Fruits (Column 1 has the list of fruits and Column 2 has the prices) Vegetables (Column 1 has the list of vegetables and Column 2 has the price).

My colleague and I were mucking about trying to break the form and we found that if no ProductFamily is selected in A1, we could type whatever we wanted in A2 without validation error, and after doing so, we could even select a Product Family and it still not prompt a problem in our nonstandard input.

To try to counter this, I created a fourth table which I called Null. It had only one cell in it and it was ="".

To test this, I then set the Data Validation to List, with the Source being =INDIRECT(IF(ISBLANK(A1),"Null","Fruit[Column1]")). With this, I found that I could still type in A2 if A1 was blank, but if A1 was not blank, I could not type. I swapped the positions so the formula was: =INDIRECT(IF(ISBLANK(A1),"Fruit[Column1]","Null")) and found that while I could see the fruit list when A1 was blank, I could still type whatever I wanted. when A1 was not blank, I only had the "" option and anything I typed prompted an error.

Note: I did try both the =INDIRECT(IF(condition,"True","False") syntax and the =IF(condition,INDIRECT("True"),INDIRECT("False") syntax and found (as expected) no difference in behavior.

I do not know why the True portion of the IF statement behaves this way, but I finally tried, =INDIRECT(IF(ISBLANK(A1),IF(ISBLANK(A1)=FALSE,,"Null"),"Fruit[Column1]")) and found that forcing the "Null" into the false condition made it work so that if I typed anything in A2 while A1 was blank, it provided an error.

For the final version it was

=INDIRECT(IF(ISBLANK(A1),IF(ISBLANK(A1)=FALSE,,"Null"),A1 & "[Column1]"))

Is there really no other way of doing this outside of digging into VBA?

Upvotes: 1

Views: 3780

Answers (1)

Michael
Michael

Reputation: 4848

Blanks in validation can be a little fickle. Blanks in dynamic validation even more so.

It seems the option to "Ignore Blanks" in the Data Validation dialog doesn't just apply to the field on which validation applies, but it also applies to source fields used in validation calculation. In your case, you should be able to use one of the simpler and more intuitive validation formulas if you just uncheck "Ignore Blanks".

In my example, I've named a single empty cell as "Blank" to simplify the validation formula:

=IF(A1="",Blank,INDIRECT("Fruit[Column1]"))

You now get an error if you try to type anything into A2 if A1 is blank.

(This introduces a minor issue that you can't clear A2 while in edit mode, i.e. you can't enter edit mode, remove all text and then press enter to confirm. You need to escape out of the cell and then hit Delete to clear all contents, which allows you to bypass the validation that doesn't allow blanks.)

Upvotes: 1

Related Questions