How do you Stop Entering Duplicate Data in a Column?

S

Satraj

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
 
R

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
 
S

Satraj

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.
 
M

Mladen_Dj

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

=COUNTIF(A:A,A1)=1
 
M

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
 
R

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
 
S

Satraj

Thankyou both there was a technical hitch.

Roger Govier said:
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
Click to expand...
 
B

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.
 
Top