You may not use reference operators or array constants for Data Validation?

[blbat]

Then How do I make a drop down list based on 3 possible values in an adjacent column?

 

[xenou]

Hi,
I think it might work if you name the range (the values in the adjacent column). Then you can use the named range in the data validation.

ξ

 

[blbat]

Thanks very much for the response xenou!

I've been bashing at this all morning, and was venting my frustration.

I have tried creating Two Named Ranges, "PickOne" and "NoTen"

"PickOne" contains the values 0,5 and 10
"NoTen" contains the values 0 and 5

I'm sorry I can't use HTML maker from this computer due to group policies, or I would show an example of the following description:

Column "A" is Labeled "Score One" and Column "B" is Labeled "Score Two"

Column "A" and Column "B" can ONLY contain a numeric value of 0,5 or 10
If either "A" or "B" has a value of 10, then the other may NOT, i.e.,
If Cell "A2" contains a "10" THEN Cell "B2" may NOT contain a 10, but Cell "B2" can have the 0 or 5

the following code for my datavalidation is where I got the error message:

=IF(H2="",(PickOne,INDIRECT(H2)),IF(H2=10,(NoTen,INDIRECT(H2)),IF(H2=5,(PickOne,INDIRECT(H2)),IF(H2=0,(PickOne,INDIRECT(H2)),""))))

I hope that is enough to go on...

 

[xenou]

That's a little unusual. I'm might prefer a conditional formatting "warning" rather than strict cell validation - easier to do.

This seems to work. If you use a "custom formula" for the validation, you want a true or false type of result.

In A2:
=IF(B2=10,OR(A2=0,A2=5),OR(A2=0,A2=5,A2=10))

In B2:
=IF(A2=10,OR(B2=0,B2=5),OR(B2=0,B2=5,B2=10))

 

[blbat]

It is unusual-

The reason is because the combined value of any given row in the two columns can't be >=20.

Your Custom Formula achieves the desired outcome.

I truly appreciate it.

regards,

blbat