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

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

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

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

=COUNTIF(A:A,A1)=1

"Satraj" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>