Data validation Alert

[Polanskiman]

Hello,

Here is a simplified set of data. As you can see there is one dropdown list that will list values from the list1 or list2 depending whether there are values or not in I12 and/or J12 cells.

All works fine. Only issue is if I delete values from I12 and J12 and then go to the DataValidation and confirm the DataValidation I get an Alert saying the following:

The list source must be a delimited list, or a reference to single row or column.

This does not happen if there is a value in either I12 or J12 and proceed to DataValidation and do the same procedure.

Why is this happening?

Book1
	H	I	J	K	L	M	N
6
7		DropDown list
8		maybe
9					List1	List2
10					yes	yes
11		Value 1	Value 2		no	no
12					maybe
13
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
I8	List	=IF(AND(I12="",J12 =""),"",IF(OR(I12="",J12 =""),selection,selection1))

 

[VBE313]

You are stating in your data validation formula IF I12 and J12 both are BLANK, then give me BLANK. You are setting your data validation to BLANK which is why that error message is popping up.

 

[Polanskiman]

I see. Not that obvious to figure out. The alert message is not very helpful.

Would there be a way of instead showing a blank, show something like "n/a"? I have tried to replace the "" in the formula by "n/a" be it doesn't show the n/a. I suppose because it's dropdown list.