Excel 2007 : Lookup last Date in a row

[richardgf]

I was wondering if someone could help me.
I am trying to design a drawing register that shows the status of a drawing.
What I need to be done is
What I need I Column CQ “status”
To pick up the last date.
Example Below

Target Date to Red Line Redline Prepared Redline Checked Date Sent to Designer Date Received from Designer As Built Checked Re-issue to WSP Date Revision Received from Designer Status
21/05/2011 03/05/2011 03/05/2011 03/05/2011
31/05/2011 03/05/2011 28/05/2011 28/05/2011


I have tried several formulas
=INDEX(CI15:CP15,1,5)
=IF(D17="","",(LOOKUP("xx",CI17:CP17)))
=IF(ISBLANK(J15),I15,IF(ISBLANK(K15),J15,IF(ISBLANK(L15),K15,IF(ISBLANK(M15),L15,IF(ISBLANK(N15),M15,IF(ISBLANK(O15),N15,IF(ISBLANK(P15),O15)))))))

The IF Statement((D17="","",(LOOKUP("xx",CI17:CP17)))) does work only if it has Words
And the other two don’t seem to like gaps in the range.
After this is done I want to do some conditional formatting with the status cells.

To Yellow 7days before the date Orange 7 days after date and Red 14 days after day.

Can anyone help me.

[zbor]

You mean this:

=LOOKUP(99^99, CI15:CP15)

[richardgf]

You mean this:

=LOOKUP(99^99, CI15:CP15)

Thanks that seems to work.

Can you explain 99^99 represents?

[zbor]

Sure... Read first part of this post: http://www.excelforum.com/2535842-post11.html

[richardgf]

Sure... Read first part of this post: http://www.excelforum.com/2535842-post11.html

Thanks that helps alot.

I am now struggling with the conditional formatting of the Status Column.

What I need is the status Column to Go
Yellow if Date is under 7 days
Orange if date is over 7 days
Red if Date is over 14days
Example if Cell CQ20 is 03/05/11 then that cell would go RED.

Can you please help me.

[zbor]

Comparing to todays date?

[richardgf]

Comparing to todays date?

Correct. I have tried doing =TODAY()-$CQ15

[zbor]

Almost there.. You missing criteria...

Put yellow backround to the cells and then:

=$CQ15-TODAY()>14 into red
=$CQ15-TODAY()>7 into orange

(in this order, first to check is it > 14)

[richardgf]

Didn't seem to work. As most of the cells went Yellow.

I did try

=(($CU15)>=14) – Red
=($CU15>0)*($CU15<=13) - Orange
=(($CU15)<=7) - Yellow

But the problem is some cells go orange and yellow. As some of the 31/05/11 go orange and some go yellow, which is rather conflicting

I have attached the spreadsheet

[zbor]

Here, see:

[richardgf]

Thanks for your help. The main problem with your attached spreadsheet is that all cells seem yellow example CQ25 = 31/03/11 but cell colour yellow but should be Red.

[zbor]

Then you need to switch > to <

(but when you do the also put now orange criteria first priority)

Edit: Hope it's OK now.