3 data validations
 

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

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

3 data validations
 

Thanks.. It worked perfectly! (just a typo on :A.. s/b $A)

"Bob Phillips" wrote:

=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