Excel - Format Column for No Duplicates 2

[grnzbra]

I can design a table in MSAccess or SQL Server to not allow duplicates. Is there any way to design a worksheet in Excel to allow the user to enter anything they want provided it doesn't already exist in that column? I have vba code to check for duplicates, but when a lot of data has been entered, the whole thing bogs down. I'd like to be able to format a column of cells for "No Duplicates".

 

[SkipVought]

Hi,

Check out Data > Validation ... the LIST option, using the MATCH() function to search the cells above.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

Try a "custom" data validation.[ ] With Excel-2010 you can apply your validation formula to the entire column, in the one operation.[ ] Maybe with other versions as well, but I have never tried.

Select the entire column, say column L.
Data > Data validation > Set allow = "Custom"
Enter [tt]=COUNTIF($L:$L,L1)=1[/tt]
into the formula box.

All done.[ ] This will allow for duplicates that appear below the cell you are entering into, as well as duplicates above.

 

[grnzbra]

Thank you both. This has been most helpful.