Just to add on...

Issue 1--You can use 'Data Validation' to avoid duplicate names being
entered to the master list in Sheet2 Column A

Select Column AFrom menu DataValidationselect 'Custom' and enter formula
=COUNTIF(A:A,A1)=1.
If you want to have a customized message you can add that to the 'Error
Alert' tab


Issue 2&3--Use Conditional Formatting. Suppose you enter the name to Column
A and the formula to lookup tag id is in ColumnB.

Select column B From menu FormatConditional Formatting For
Condition1Select 'Formula Is' and enter the below formula

'if you are using your original formula in Col B which returns #NA then use
=AND(A1<"",ISNA(B1))

'if you are using the formula which Biff has suggested to handle #NA and
return blank
=AND(A1<"",B1="")

Click Format ButtonPattern and select your color (say Red) and hit OK


If this post helps click Yes
---------------
Jacob Skaria


"NDBC" wrote:

I have a worksheet with a list of rider names and rider id tag no. EG.


A B
John Smith 256987
Rod Brown 236478

In another worksheet I use a vlookup of the riders name to return there id
tag number. ie. vlookup("john smith",sheet2!A1:B10,2,False)

This can fail for three reasons.

1. There are multiple people with the same name.
2. The name is spelt incorrectly
3. The name and tag id have not been entered in the original worksheet (ie.
first time rider)

What I would like to do to help prevent reasons 2 and 3 from occuring is
make the cell that the vlookup formula is in go red if it can't find a tag id
number (ie brings back #N/A).

Can this be done without vba.

I would also appreciate any ideas on checking for multiple riders with the
same name. Once each occurence occurs the names would be changed (ie initials
added or something along those lines) but it would be good to have a check to
make sure that when a first time rider comes along he does not have the same
name as a rider currently on the list.

Thanks