In Excel, how can I tell the program to disallow duplicate data i.

[gthawkster]

I would like to use a column to identity each row in a list as a separate
item #. I can't have duplicates in this column and would like the program to
prevent someone from entering an ID# in twice. Any ideas?

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/NoDupEntry.htm

 

[Jason Morin]

Remember that Validation can be overwritten easily with
copy/paste:

http://www.contextures.com/xlDataVal07.html#Unique

HTH
Jason
Atlanta, GA

 

[Jason Morin]

To help prevent accidential copying and pasting over
Validation cells, you could employ this technique from J.
Walkenbach:

http://j-walk.com/ss/excel/tips/tip98.htm

HTH
Jason
Atlanta, GA

 

[Harlan Grove]

Jason Morin wrote...

To help prevent accidential copying and pasting over Validation cells, you could
employ this technique from J. Walkenbach:

http://j-walk.com/ss/excel/tips/tip98.htm

....

Not reliable. At least not for XL97.

Given the setup in this tip, define validation as whole numbers between 0
and 10. Enter -500. Excel will display a dialog about the invalid entry.
Click the Retry button (or press [Enter]), then press [Ctrl]+C, [Esc],
[Ctrl]+V in sequence. Voila - there's an invalid entry in the cell that the
wonderful Change event handler did squat all to prevent. Pasting *text* into
cells leaves validation rules as-is, though completely ignored. This is also
true for pasting special as values. Excel's Data Validation is a toy feature
providing no more true security than worksheet or workbook protection
passwords.

The only *SECURE* way to implement validation involves both using Calculate
event handlers and hardcoding the validation rules within them to provide
entry-time validation, *and* using formulas, possibly in defined names, to
check all critical cells and return error values if there are any invalid
entries or innocuous values if all cells contain valid entries. Then use
those check values in all downstream calculations to ensure that *ANY*
invalid entries result in *ALL* downstream formulas returning error values.
*NOTHING* gets users to clean up their entries quicker than giving them
NOTHING BUT GARBAGE if they're careless or worse.

 

[Harlan Grove]

I would like to use a column to identity each row in a list as a separate
item #. I can't have duplicates in this column and would like the program
to prevent someone from entering an ID# in twice. Any ideas?

How exactly is your workbook used by these other users? What sort of ID #s
are they entering? Are your users assigning these ID #s themselves? If so,
computers are MUCH, MUCH BETTER at generating unique ID #s or serial #s than
humans, so it may be better to have the workbook generate these #s.