View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 3 data validations

=AND(COUNTIF(:A:$C,A2)<2,ISNUMBER(A2),LEN(A2)=8)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mohan" wrote in message
...
Hi
I have data in column A, B and C (these are account numbers). I need to
prevent
a) duplicate entries - which is working fine with "COUNTIF($A:$C,A2)<2"
formula in custom data validation

I also need to add two more validations
b) only numbers are allowed (no letters or characters)
c) the lenght must be 8

Can some one help me with a formula for all 3 of them or is there a better
way of doing this in VBA (instead of data validation)

Thanks