Restricting cells to unique values

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

 

[Guest]

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.

 

[Ed Ferrero]

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