Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Satraj
 
Posts: n/a
Default 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


  #2   Report Post  
Roger Govier
 
Posts: n/a
Default 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


  #3   Report Post  
Satraj
 
Posts: n/a
Default 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


  #4   Report Post  
Mladen_Dj
 
Posts: n/a
Default 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




  #5   Report Post  
Mladen_Dj
 
Posts: n/a
Default 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






  #6   Report Post  
Roger Govier
 
Posts: n/a
Default 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


  #7   Report Post  
Satraj
 
Posts: n/a
Default 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



  #8   Report Post  
Barb Reinhardt
 
Posts: n/a
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loading Column Data with blank Rows into Data Validation Box ExcelMonkey Excel Worksheet Functions 3 October 13th 05 06:09 PM
Finding DUplicate Data set in Worksheets SAT Excel Worksheet Functions 4 September 17th 05 11:50 PM
2 Column Data lookup Hari Excel Discussion (Misc queries) 2 June 15th 05 07:54 AM
Sort pages? David Excel Discussion (Misc queries) 15 May 13th 05 11:33 PM
How do I sort a column of data and have each data row sort accordi Oedalis Excel Discussion (Misc queries) 1 March 18th 05 12:52 AM


All times are GMT +1. The time now is 03:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"