Data Validation and "Ignore Blank"

[MikeMcCollister]

I have three adjacent cells which I want to restrict numbers to be entered into only one. So I have a data validation scheme setup so that I am using for cell A2

=AND(ISNUMBER(A2), ISBLANK(B2), ISBLANK(C2))

If I have "Ignore blank" checked, this does not work, as shown here. Why is that?

Thanks,

Mike

 

[earthworm]

Apply this formula in Data validation and Uncheck click blank and apply in all three cell

=OR(AND(ISNUMBER($A2),ISBLANK($B2),ISBLANK($C2)),AND(ISNUMBER($B2),ISBLANK($A2),ISBLANK($C2)),AND(ISNUMBER($C2),ISBLANK($A2),ISBLANK($B2)))

after this user will be allowed to input only 1 number in a row . otherwise it will give error as required

 

[Fluff]

Another option, select A2:C2 & use this

=AND(ISNUMBER(A2),COUNTIF($A2:$C2,"")=2)

 

[MikeMcCollister]

Thanks all for your help. That made a difference.

Regards,

Mike

 

[Fluff]

Glad we could help & thanks for the feedback.