compare Sheet1 against Sheet2 with highlighting results in Sheet1

[mariposa]

hello everyone
...Now, i am stuck with how can i do this another tedious and hard excel problem i dealt with. How can i compare a master list in Sheet1 against actual values in Sheet 2 and with a result of highlighting the values that are in Sheet 2 in "Sheet 1".I hope i am clear...Sorry for my poor English. I will try this way; whatever values i have in sheet 2 that are found in Sheet 1-it should be highlighted (in any color) in Sheet1. I have attach a sample file. Please help me when you can. I would be very very thankful to you.

Im new to this site, I am actually learning how to post and be registered...and this is the best way so far that i think i can do to ask help from you, the experts.

best regards,
mariposa

[tigeravatar]

mariposa,

Give the following a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[ChemistB]

Assuming your data on sheet2 is in a single column. Use a defined name to name that list (I chose "List"). Then select B3:Q24 in Sheet1 and go to conditional formatting > use formula

=AND(B3<>0,ISNUMBER(VLOOKUP(B3,Sheet2!$A:$A,1,FALSE)))
format as desired.

[mariposa]

hi tigeravatar,

thanks for the quick reply...ill give this a try. hope it will work. i get back to you when its done.

many thanks,
mariposa

[arthurbr]

Mariposa

please do not use polls unnecessarily - Thx

[mariposa]

Assuming your data on sheet2 is in a single column. Use a defined name to name that list (I chose "List"). Then select B3:Q24 in Sheet1 and go to conditional formatting > use formula

=AND(B3<>0,ISNUMBER(VLOOKUP(B3,Sheet2!$A:$A,1,FALSE)))
format as desired.

Hello chemist,
thank you for your reply...
but sad, it didnt work well. i used define name "list" as you were using and able to highlight B3:Q24 in sheet 1but when i click conditional formatting, then choose highlight cells rules, then "equal to" then i input your formula----it sends me this message "you can not use references to other worksheets or workbooks for conditional formatting criteria."
am i doing what you ask? it seems, not working yet. Pls. advise. thank you.

mariposa

[arthurbr]

Please refrain from quoting entire posts.You clutter the forum and make threads hard to read !

[ChemistB]

In Excel 2007,
New Rule > "Use a formula to determine which cells to format"
Hope that helps.

[mariposa]

Hi chemistB,

i tried what you suggest, but it still says ""you can not use references to other worksheets or workbooks for conditional formatting criteria".

im sad, the formula didnt work for me.

pls. help.

mariposa

[Haseeb Avarakkan]

Hello Mariposa,

Define a name range foe the sheet2 data, Then use it in the CF as

=AND(a1<>"",MATCH(A1,NamedRange,0))

See the attached.

[ChemistB]

The easiest way to define a name is to select your values (i.e. sheet2 A2:A50) and in the name box (upper left side white text box where you usually see a cell reference like A2)type in a name and hit Enter. Then you can use that name in your formula.

[mariposa]

hi ChemistB,

thanks for helping me figuring out how to define a name, i did it now....i also called it "list". Now, my question is, where in the formula,=AND(B3<>0,ISNUMBER(VLOOKUP(B3,Sheet2!$A:$A,1,FALSE))), you've given me, will i insert the name "list".

Pls. help..
i really appreciate your effort to guide me.
thanks in advance.
mariposa

[ChemistB]

=AND(B3<>0,ISNUMBER(VLOOKUP(B3,List,1,FALSE)))

[mariposa]

hi Haseeb,

thank you so so much! it works...wow...great job!

and thanks to chemist for the input too...i have now what i need. thanks..

mariposa