data validation across multiple columns
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 structu 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. |
data validation across multiple columns
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 B2:D2 and then Data Valisation and the formula: =AND(B2<2,COUNTIF($B2:$D2,0)<=1) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
All times are GMT +1. The time now is 11:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com