Avoiding Duplicates

I would like to apply Data Validation to entries in a cell to prevent
duplicates, but I need the facility to enter a "valid duplicate" eg When
entering a surname that has been used before a warning is returned but this
can be ignored if entry is valid.

Hi,

say your area is A1 to C20. Select it go to Data Validation and enter.

=COUNTIF($A$1:$C$20,A1)=1

VBA Noob

Use a formula of

=COUNTIF($A$2:$A$209,A2)>1

where A2 is the DV cell, and on the Error Alerttab of DV, set the error
style type to Warning

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Mald" <Mald@discussions.microsoft.com> wrote in message
news:7C7CBDB7-EB31-4D1F-8168-DA2422EEF9C3@microsoft.com...
> I would like to apply Data Validation to entries in a cell to prevent
> duplicates, but I need the facility to enter a "valid duplicate" eg When
> entering a surname that has been used before a warning is returned but
this
> can be ignored if entry is valid.

Sorry, should be = 1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Phillips" <bob.NGs@xxxx.om> wrote in message
news:OKR$MSXtGHA.3264@TK2MSFTNGP03.phx.gbl...
> Use a formula of
>
> =COUNTIF($A$2:$A$209,A2)>1
>
> where A2 is the DV cell, and on the Error Alerttab of DV, set the error
> style type to Warning
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Mald" <Mald@discussions.microsoft.com> wrote in message
> news:7C7CBDB7-EB31-4D1F-8168-DA2422EEF9C3@microsoft.com...
> > I would like to apply Data Validation to entries in a cell to prevent
> > duplicates, but I need the facility to enter a "valid duplicate" eg When
> > entering a surname that has been used before a warning is returned but
> this
> > can be ignored if entry is valid.
>
>

Thanks Had done formula correct but not altered Error Alert tab works fine

"Bob Phillips" wrote:

> Use a formula of
>
> =COUNTIF($A$2:$A$209,A2)>1
>
> where A2 is the DV cell, and on the Error Alerttab of DV, set the error
> style type to Warning
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Mald" <Mald@discussions.microsoft.com> wrote in message
> news:7C7CBDB7-EB31-4D1F-8168-DA2422EEF9C3@microsoft.com...
> > I would like to apply Data Validation to entries in a cell to prevent
> > duplicates, but I need the facility to enter a "valid duplicate" eg When
> > entering a surname that has been used before a warning is returned but
> this
> > can be ignored if entry is valid.
>
>
>