Data validation two criteria
 

Is it possible to do two criteria for cells entered in a column using
data validation. I already prevent numeric entries by using a custom
validation criteria eg =NOT(ISNUMBER(A5)) but I would also like to
prevent or alert to duplicates in the column using another custom
criteria eg =COUNTIF($A$1:$A$100,A5)<=1. Can these two be combined in
any way to achieve the desired result? I value any assistance.

Kind Regards
Graham Haughs
Turriff
Scotland

Data validation two criteria
 

=AND(NOT(ISNUMBER(A5)) ,COUNTIF($A$1:$A$100,A5)<2)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Graham Haughs" wrote in message
...
Is it possible to do two criteria for cells entered in a column using
data validation. I already prevent numeric entries by using a custom
validation criteria eg =NOT(ISNUMBER(A5)) but I would also like to
prevent or alert to duplicates in the column using another custom
criteria eg =COUNTIF($A$1:$A$100,A5)<=1. Can these two be combined in
any way to achieve the desired result? I value any assistance.

Kind Regards
Graham Haughs
Turriff
Scotland

Data validation two criteria
 

Thanks Bob. I was trying If statements and failing miserably, but once
again I am shamed with the simplicity, although it is only simple if you
know what you are doing. Many thanks.

Graham

Bob Phillips wrote:
=AND(NOT(ISNUMBER(A5)) ,COUNTIF($A$1:$A$100,A5)<2)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Graham Haughs" wrote in message
...

Is it possible to do two criteria for cells entered in a column using
data validation. I already prevent numeric entries by using a custom
validation criteria eg =NOT(ISNUMBER(A5)) but I would also like to
prevent or alert to duplicates in the column using another custom
criteria eg =COUNTIF($A$1:$A$100,A5)<=1. Can these two be combined in
any way to achieve the desired result? I value any assistance.

Kind Regards
Graham Haughs
Turriff
Scotland

Data validation two criteria
 

The thing to remember with Data Validation (and Conditional Formatting) is
that it only needs a TRUE result to fire it, so the IF is not necessary,
just a test that returns TRUE or FALSE.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Graham Haughs" wrote in message
...
Thanks Bob. I was trying If statements and failing miserably, but once
again I am shamed with the simplicity, although it is only simple if you
know what you are doing. Many thanks.

Graham

Bob Phillips wrote:
=AND(NOT(ISNUMBER(A5)) ,COUNTIF($A$1:$A$100,A5)<2)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Graham Haughs" wrote in message
...

Is it possible to do two criteria for cells entered in a column using
data validation. I already prevent numeric entries by using a custom
validation criteria eg =NOT(ISNUMBER(A5)) but I would also like to
prevent or alert to duplicates in the column using another custom
criteria eg =COUNTIF($A$1:$A$100,A5)<=1. Can these two be combined in
any way to achieve the desired result? I value any assistance.

Kind Regards
Graham Haughs
Turriff
Scotland