Conditional Format - Highlight NON Duplicate Values (on another sheet)

[surkdidat]

Hi
On sheet1, column B, I have a list of reference numbers. In sheet2, column A these may or may not appear.
In Sheet1, I would like the Cell in Column B highlighted IF it does NOT appear in Sheet2, column A
Blanks should also be ignored


Many thanks

 

[DanteAmor]

You can not use references to other books or sheets for conditional format.

You could use a macro to look up the data in sheet2 ir it does NOT appear then fill the cell with a color

 

[Fluff]

Select B2 downwards & try this formula
=AND(B2<>"",NOT(ISNUMBER(MATCH(B2,Sheet2!A:A,0))))

Depending on your version of Xl this may not work.

 

[Marcelo Branco]

You can not use references to other books or sheets for conditional format.

It's possible using named ranges.
See
https://www.youtube.com/watch?v=0BXka20-nOk

M.

 

[Fluff]

With 2010 onwards you can do it directly

 

[DanteAmor]

It's possible using named ranges.
See
https://www.youtube.com/watch?v=0BXka20-nOk

M.

Okay, I forgot the rank names for a moment

 

[pgc01]

Hi

Try also

=AND(B2<>"",COUNTIF(Sheet2!A:A,B2)=0)