data validation across multiple columns

[AKrobbins]

I'm attempting to include a stop message when a person enters "0"
across multiple columns. The data entry person has the option to use
"1" or "0", however if they use zeros across the multiple columns I
want the person to be alerted to this error. Here is the the data
structure:

ID V1 V2 V3
1 0 0 0
2 1 0 0

I'm currently using this formula to flag cases that reported all "0"
in the three columns: =IF(SUM(V1:V2)=0,"Missing", "NA".

However I want to use the data validation tool instead so the person
doing the data entry gets a warning that a data entry error occurred.
I used the above formula in Data Validation: Customs and when I tested
to see if an error message came up after entering "0" in all three
columns no message came up. I am not macro savvy so I was hoping I can
solve this issue with formula. Any help is appreciated.

 

[Claus Busch]

Hi,

Am Fri, 6 May 2011 07:38:42 -0700 (PDT) schrieb AKrobbins:

ID V1 V2 V3
1 0 0 0
2 1 0 0

However I want to use the data validation tool instead so the person
doing the data entry gets a warning that a data entry error occurred.
I used the above formula in Data Validation: Customs and when I tested
to see if an error message came up after entering "0" in all three
columns no message came up. I am not macro savvy so I was hoping I can
solve this issue with formula. Any help is appreciated.

V1 in column B, V2 in C ans so on. Select B22 and then Data Valisation
and the formula:
=AND(B2<2,COUNTIF($B2:$D2,0)<=1)


Regards
Claus Busch