multi condition format? lookup and date condition valid for.

**D_Rennie** · 08-13-2009, 01:38 AM

Hello all.

I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.

i have put a little more info into the example file to try and help explian.

thankyou.

**NBVC** · 08-13-2009, 08:05 AM

Formula in E6:

=IF(C6<=SUMIF(Sheet1!A:A,A6,Sheet1!D:D),"Yes","No") copied down.

You can't really clear the formatting after 10 days from today() as Excel doesn't store today's date as a fixed date for tomorrow... (unless you use VBA)

An alternative, is to set colour back 10 days after the date in column D...

If you want that, then apply this conditional format at the top of the conditional format list (i.e make it the first format rule).

=AND($E6="No",TODAY()<$D6+10)

**D_Rennie** · 08-13-2009, 09:13 AM

Thankyou.
The Formula does exacly what i asked for. Though can you help with it just a little more.
I would also like if say cell C6 is blank then E6 should also be blank.

Also with the condition formatting. can the entire row color be changes not just the single cell.

Thankyou Again.

**NBVC** · 08-13-2009, 09:15 AM

Try:

=IF(C6="","",IF(C6<=SUMIF(Sheet1!A:A,A6,Sheet1!D:D),"Yes","No"))

If you select the entire range (all rows) and apply that conditional format, then the whole row will highlight.

**D_Rennie** · 08-13-2009, 09:53 AM

Thankyou.

Exaclly what i wanted.

**D_Rennie** · 08-13-2009, 11:31 AM

With
=IF(C6="","",IF(C6<=SUMIF(Sheet1!A:A,A6,Sheet1!D:D),"Yes","No"))

Is there a way to change the cell value to Yes or No only once.
So if the value's are changed on sheet1 it will not change the cells on sheet2 that aready contain a Yes or No.
This way later down the track i can see how often in a time period, item's had to be backordered.

thankyou.