Date highlighting (Conditional Formatting) relative to other date for common reference (PO Number)

[steveh8204]

Is it possible to use Conditional Formatting to do the following:

On sheet 1: Look up a date in Column L which relates to a value (Order number) in Column F, this then needs to cross reference with a date in sheet2 in Column E which relates to the same PO in Column A (on sheet2). Ideally the date on sheet1 would be Green if it matches the date on sheet2, Amber if it is older than the date on sheet2 or Red if it is newer.

I hope I've explained it well.

Sheet1

Order Number (F) - Date (L)

Sheet2

Same Order Number (A) - Date (E)

Result = Date in sheet1, column L: Green (matches), Amber (older than date in sheet2), Red (newer date than date in sheet2)

Oh and the PO numbers are usually in different rows (don't ask for much do I?).

 

[Special-K99]

Try this

Select the range to highlight

Conditional Formatting
New Rule
Use a formula to determine...

=VLOOKUP(Sheet1!F1,Sheet2!A$1:A$:E$1000,5,0)=L1
format as green
=VLOOKUP(Sheet1!F1,Sheet2!A$1:A$:E$1000,5,0) < L1
format as amber
=VLOOKUP(Sheet1!F1,Sheet2!A$1:A$:E$1000,5,0) > L1
format as red

Depending on your version of Excel CF may not be able to look at another sheet.
If this is the case then you need to define Sheet2!A$1:E$1000 as a Named Range and use that in the formulas instead.

 

[steveh8204]

Nice one, works like a charm, this tip has also come in very handy:

Depending on your version of Excel CF may not be able to look at another sheet.
If this is the case then you need to define Sheet2!A$1:E$1000 as a Named Range and use that in the formulas instead.

I've had loads of problems with CF which is probably because of this. A bit strange as I have the newest version. Oh well at least I know the cause now so I will have to make sure I use Named Ranges in future. Thanks very much for your help.