Preventing Duplicates on Entry

  • Thread starter Thread starter knp0611
  • Start date Start date
K

knp0611

I am now trying to use the function that prevents a user
from entering a duplicate number in a column (in my case,
invoice number). Using the formula =COUNTIF
($A$1:$A$50,A1)=1, will only alert the user if there is a
duplication of the invoice number that was entered in
cell A1.

Am I missing something?

Please help.

Thank you.
 
Hi
try the following:
- select your entry range A1:A50
- goto 'data validation' and enter the formula
=COUNTIF($A$1:$A1,A1)=1
 
Yes you have missed something, to apply the validation
select cell A1:A50 with A1 as the active cell,
then apply data>validation>allow>custom and use

=COUNTIF($A$1:$A$50,A1)<=1

not the relative reference for the last A1, click OK
 
That's it. Thanks so much!
-----Original Message-----
Hi
try the following:
- select your entry range A1:A50
- goto 'data validation' and enter the formula
=COUNTIF($A$1:$A1,A1)=1

--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
This one did not work. I used =COUNTIF($A$1:$A1,A1)=1
from Frank Kabel, and that works.

Thanks so much for your help.
 
Back
Top