Restricting cells to unique values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm entering invoice identification numbers and want to format the column so
that if a duplicate number is entered by mistake it will be flagged as an
error. I'm using Excel 2002. Thank you in advance for any help.
 
Maybe you could use conditional formatting. If your inputs are A1:A10 select
A1:A10, Format/Conditional formatting

Formula Is: =COUNTIF(A$1:A1,A1)>1

and select a cell or font format.
 
Hi Todd,

1) select the first cell in the column, say cell B1 for column B
2) use menu item Data -> Validation
3) set Validation criteria to Custom
4) enter =COUNTIF(B:B,B1)<=1 in the formula box
5) click OK
6) copy cell B1 to all of column B (just click on the column B heading to
select the whole column)

Now you can only enter unique valueds in column B.

You can revisit Data -> Validation to provide more meaningful error messages
if you wish.

Ed Ferrero
www.edferrero.com
 
Back
Top