MrExcel Publishing
Your One Stop for Excel Tips & Solutions

validating data.....without repeating!!

Posted by zen on June 06, 2001 2:39 AM

i've been putting together a master data sheet with peoples names telephones login id's etc., i'm using 'list' validation on login's, how can i make sure that someone doesn't put the same one i twice for different people, i've used conditional formating in the short term, to highlight red if the countif is > 1,
i'd rather some form of error message to alert the user that it isn't valid. ideally it could ask the user if they want to keep the number or change it, in both the new input and the old.

any help is most definately appretiated


p.s. i still want to use 'list' validation

Posted by Johan on June 06, 2001 7:34 AM

Duplicate entries can be prevented by Conditional Formatting with the following formula :-


Posted by zen on June 06, 2001 8:00 AM

i need to do this without conditional formating

as i have mentioned, i'm using conditional formating already as a short term solution
i need it to compare the data and then the user can change either one that is wrong

i need it like this as i've hundreds of id's that the user has to search through if it's, say, highlighted red


Posted by Peter on June 06, 2001 10:18 AM

Re: i need to do this without conditional formating

More than one condition is allowed in Conditional Formatting. Can't you do what you want by setting whatever conditions you need?
If not, VBA will be required.

Posted by ZEN on June 07, 2001 8:30 AM


that's a pronblem i've already posted and still haven't got the answer to.


if you've got answer to that lot i'll be eternally greatfully.