How do you Stop Entering Duplicate Data in a Column?
 

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

How do you Stop Entering Duplicate Data in a Column?
 

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


Satraj wrote:
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

How do you Stop Entering Duplicate Data in a Column?
 

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.

"Satraj" wrote:

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

How do you Stop Entering Duplicate Data in a Column?
 

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

=COUNTIF(A:A,A1)=1

"Satraj" wrote in message
...
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

How do you Stop Entering Duplicate Data in a Column?
 

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



"Satraj" wrote in message
...
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.

"Satraj" wrote:

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

How do you Stop Entering Duplicate Data in a Column?
 

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


Satraj wrote:
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.

"Satraj" wrote:


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

How do you Stop Entering Duplicate Data in a Column?
 

Thankyou both there was a technical hitch.

"Roger Govier" wrote:

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


Satraj wrote:
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.

"Satraj" wrote:


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

How do you Stop Entering Duplicate Data in a Column?
 

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.





"Satraj" wrote in message
...
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