Conditional formatting based on another sheet

I would like to search sheet A for all products and compare them to the master sheet.

If a product is listed in sheet A and not in master, I would like it to format a cell on a comparison sheet in one manner and if a product is listed in master but not in sheet A, I would like it to format the same cell in another format. If they are the same, no format is needed.

ie.

Sheet A
apple
orange
pear

Master
apple
banana
orange

Comparison
apple Cell is normal
banana Cell is highlighted yellow
orange Cell is normal
pear Cell is highlighted green

one way would be to

Say the range with the data in on one sheet was called master (Insert, Name, Define) you would set your conditional format on the other sheet as follows -

Highlight all data all data in other sheet.
select Format, Conditional Format

Formula Is, =match(a2,Master,0)
(asssumes a2 is the first highlighted cell in your list)
Then pick format type

R

When I input this formula as a conditional format, it tells me that I cannot reference another sheet as part of a conditional format. This is where my delima is. How do I do this another way.?

Goof

To get round this you need to name the range with the data in on the other sheet.
To do this highlight the range with the data in and then select Insert, Name, Define from the menus - and give the range a name eg master or something else.

with what you are doing you will need to name both ranges on both sheets so that you can refer to the other sheet in your formula.

R

Try

=ISNA(MATCH(A1,master,0))
Will return true for no match

R

Thank you!!