format cell when a formula error occurs

[NDBC]

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

 

[T. Valko]

vlookup("john smith",sheet2!A1:B10,2,False)

For the #N/A problem...

A1 = John Smith

=IF(COUNTIF(Sheet2!A$1:A$10,A1),VLOOKUP(A1,Sheet2!A$1:B$10,2,0),"")

That will return a blank.

For the duplicate name problem:

=IF(COUNTIF(Sheet2!A$1:A$10,A1)>1,"Duplicate","Unique")

 

[NDBC]

Thanks Biff, that works well. I have even combined them so both things are
checked at the same time. I have now finally worked out how to use
conditional formatting effectively and have set it up so it formats the cells
red if they are empty or duplicate for visual cues as well.

Thanks

 

[Jacob Skaria]

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 A>From menu Data>Validation>select '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 Format>Conditional Formatting>> For
Condition1>Select '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 Button>Pattern and select your color (say Red) and hit OK


If this post helps click Yes

 

[NDBC]

Jacob,

Thanks again. Some very good protection ideas there and I will implment the
lot. One other error I just thought of is when a second rider who just
happens to have the same name as a current rider turns up for the first time.
There will only be one name in the list on sheet2 so it could be looked up
twice and the same tag id number returned for both. Just thought of an idea
for this whilst typing. (putting problems into words helps clarify things in
my brain). I could use the data validation trick you showed me below to
ensure no tag id's are used twice.

Thanks, you've helped me without even trying now. A new first.

 

[T. Valko]

You're welcome. Thanks for the feedback!