Checking and indicating data from one sheet to another

**dellf** · 01-29-2009, 07:50 AM

Hi,

sheet1 has a list of names
sheet2 has a list of names and some of the names are the same as sheet1.

What I want is for the names on sheet1 to indicate (preferably change text colour) if they are on sheet2.

Thanks

**NBVC** · 01-29-2009, 08:57 AM

Select your list in Sheet 1 and go to Format|Conditional Formatting

Select Formula Is from 1st dropdown and enter formula: =MATCH(A1,INDIRECT("Sheet2!A:A"),0)

where A1 is the top most cell in your selection (change as necessary) and Sheet2!A:A contains the names to match up with.

Click Format and choose colour scheme.

**dellf** · 02-03-2009, 08:28 AM

Hi,

Thanks for the reply, however the problem has gotten worse!?!
Some of the names do as the conditional formatting rule should, whilst others don't.
I can't understand why.

**NBVC** · 02-03-2009, 08:54 AM

Make sure the ones you think should match are indeed exact matches... ie. spelled the same, no extra spaces or other characters, etc... Also if you are matching numbers, they must both be number formats.

If you can't figure it out, post an example workbook, showing the problem.

**dellf** · 02-04-2009, 11:23 AM

Thank you very much
please see my example

**NBVC** · 02-04-2009, 12:38 PM

rather than me going back and forth to physically check inaccuracies, can you give a couple of examples of where things are not right?

thanks

Edit: Actually, I did just notice, that, as I had mentioned would be a possible problem, in your sheet1 there are many names at the bottom that do have an extra space at the end.. that is why you don't get matches... you should eliminate those spaces...

but you can change the CF formula to this:

=MATCH(TRIM(A1),INDIRECT("list!d2:d414"),0)

to get matches as if the spaces were not there.

reselect column A and invoke the CF dialogue.. replace the formula.