conditional formatting

[mechpm]

Useing conditional formatting
I would like to change text when items are due or over due. I have found something close on this great web site. However, it falls short of my goals. I will now attempt to provide an example.

A1
Release Date
1-1-02

B1(=A1+15)
Ship Dates
1-16-02

C1 (=B1+5) Date Needed
1-21-02


D1
E T A
1-17-02


E1=IF(c1>d1,"NO","YES")
Quick ship
Yes

F1 (=D1-C1)
No Headed
-4

G1 =IF(F1<1,"Day Q Ship","Days Slack")
Quick Ship
4 Day Q Ship

My future goals with this mess are too:

1. In Cell D1: change it to blue when 5 days out and red when overdue.

2. Cell F1 has no header because I have it formatted to appear to the user as one cell. I would like these cells F & G to change colors when E1 returns yes and another color when it returns no.

I no how to format the text however, I need the formulas to make the above happen.


yes dear im cumming
gotta go hope you can help

Thx

RW




[ This Message was edited by: mechpm on 2002-05-14 18:00 ]

[ This Message was edited by: mechpm on 2002-05-14 18:03 ]

[ This Message was edited by: mechpm on 2002-05-14 18:05 ]

[Yogi Anand]

Clarification needed ...
What feeds cell D1?
What is in cell F1? You say you have no header for F1, and you do have headers for other cells. Your headers should be in row1, then what ever you describe as being in cell A1 should be in cell A2, and so on.
It might be helpful if you post a few rows of your data.

Regards!

[mechpm]

[quote]
On 2002-05-14 18:10, Yogi Anand wrote:
Clarification needed ...
What feeds cell D1?
What is in cell F1? You say you have no header for F1, and you do have headers for other cells. Your headers should be in row1, then what ever you describe as being in cell A1 should be in cell A2, and so on.
It might be helpful if you post a few rows of your data.

Regards!
[/quote

In my example

A2
Release Date ( is the Header
1-1-02 (date entered by user)

B2(=A2+15) Formula in b2
Ship Dates (is the header )
1-16-02 data returned by =A1+15

C2 (=B2+5)
Date Needed (Header)
1-21-02 (DATA RETURNED BY =B2+5)


D2
E T A (Header)
1-17-02 (user input)


E2=IF(c2>d2,"NO","YES")
Quick ship (HEADER)
Yes

F2 (=D2-C2)
No Headed
-4

G2 =IF(F2<1,"Day Q Ship","Days Slack")
Quick Ship (HEADER)
4 Day Q Ship

THIS MAY HELP. IT WAS A LITTLE CONFUSING, IN THAT THESE ARE NOT THE CELLS USED IN THE ACTUAL WORK SHEET ( LEFT OUT DATA LIKE LINE ITEM, P O #, ECT.)

IM NOT SURE HOW TO POST A FEW ROWS OF DATA

HOPE THE ABOVE WILL HELP YOU.


THX R W

[Yogi Anand]

Thanks mechpm for the clarification.
The conditional format for cell D2 ...

Condition1 ... Formula Is =F2<=-5 -- Paste color 'blue'
Condition2 ... Formula Is =F2>0 -- Paste color 'red'

Conditional format for cell E2 ...

Condition1 ... Formula Is =E2="No" ... Paste color 'pink'

Condition2 ... Formula Is =E2="Yes" ... Paste color 'green'

Please note to be able to handle negative dates, you will have to Excel's date system turned to 1904 date system.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

[mechpm]

On 2002-05-14 20:21, Yogi Anand wrote:
Thanks mechpm for the clarification.
The conditional format for cell D2 ...

Condition1 ... Formula Is =F2<=-5 -- Paste color 'blue'
Condition2 ... Formula Is =F2>0 -- Paste color 'red'

Conditional format for cell E2 ...

Condition1 ... Formula Is =E2="No" ... Paste color 'pink'

Condition2 ... Formula Is =E2="Yes" ... Paste color 'green'

Please note to be able to handle negative dates, you will have to Excel's date system turned to 1904 date system.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

thx


Worked like a charm. I thought excel in it’s self was the greatest thing since sliced bread.

However, I may have prejudged. This web site / msg. boards could very well beat all.

This is an Information Junkies Dream.