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

=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >
> >>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
> >
> >
> >