Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loading Column Data with blank Rows into Data Validation Box | Excel Worksheet Functions | |||
Finding DUplicate Data set in Worksheets | Excel Worksheet Functions | |||
2 Column Data lookup | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) |