How do you Stop Entering Duplicate Data in a Column?

[Guest]

Say if I had the folloing data in column A1:A3 and I try to enter 300 in A4
again, using data validation I want to be able to be stopped from entering
that value again.
Column A
200
300
400

 

[Roger Govier]

Hi

Set the range of cells you want to apply Data Validation and choose Custom
and enter the following in the white pane.
=COUNTIF(A:A,A1)<=1

Regards

Roger Govier

 

[Guest]

What I mean is it seems to work for all of column A by using data validation
and custom =COUNTIF(A:A,A1)<2. But if I want to do a section of the columna
say A50:A60 I can't get it to work.

 

[Mladen_Dj]

In Data Validation dialog select "Custom", and enter formula:

=COUNTIF(A:A,A1)=1

 

[Mladen_Dj]

If you want to use range in column, put the absolute address for range
($A$50:$A$60), so your formula should look like:

=COUNTIF($A$50:$A$60,A50)=1

 

[Roger Govier]

Hi

You need to make the values Absolute if you are not using whole of column.
=COUNTIF($A$50:$A$60,A50)<2

Regards

Roger Govier

 

[Guest]

Thankyou both there was a technical hitch.

Hi

You need to make the values Absolute if you are not using whole of column.
=COUNTIF($A$50:$A$60,A50)<2

Regards

Roger Govier

 

[Barb Reinhardt]

Let's say you want to do data validation in cells A1:A100.

Select those cells from A1 to A100.

On the data validation entry, select Custom and enter

=countif(A1:A$1,A1)=1

Enter the appropriate error alert.

If it were me, I'd probably change the color of the cells that are being
validated so that I'd know it was there.