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

zen

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 :-

=Countif($A$1:$A$100,A1)=1

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

zen

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

PETER, THAT'S ANOTHER PROBLEM ENTIRELY SEE...

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

18195.html

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

zen