Excel 2007 : Rule to supersede conditional formatting

[dreinisch]

Right now I have an excel spreadsheet I'm working to manage a large pipeline at work. Using conditional formatting, I have columns that will change colors based on the date in relation to today (entered in C2).
In columns K, L, P, Q, R, S, T all feed off of this. They will change colors based on how old they are in relation to today's date (drawing from C2). What I want to do is supersede that rule if I enter in a date into a cell in column W.

For example... if Cell Q15, S15 and T15 are now red because they are old... but they would not be old if they drew from the date found in cell W15 instead of C2.

Meaning...a date may not always be entered in a cell in column W....but, if it is...the rules then draw from that as opposed to C2.

[NBVC]

Add a condition to the rules..

e.g. =AND(W2<>"",your_current_formula)

[MarvinP]

Hi dreinisch.

If you have "=Today()" entered into cell C2 and all the colors come from that, why don't you just enter a different date into C2. You can change the C2 back to Today() after seeing what other dates do.

I'd need a small sample to give a much better answer.

[dreinisch]

Marvin, sure thing man.....just uploaded sans private info...... hope you understand.

[dreinisch]

@ NBVC....I tried =AND(W2<>"",your_current_formula) But that didn't quite work. My current formula in the cells in column W that are drawing from C2 is =IF($V8="NO","TBD","") The number "8" is the row number, so naturally that varies depending on the row.

The cells I need to first draw from today's date (C2) are in K, L, P, Q, R, S, T.
If a date is entered into column W..... I need the date in column W to supersede the date found in C2. Meaning they may still adhere to the rules in the CF that is set up for them..... BUT.... instead of changing colors based on today's date, they would change colors then based on the new date in column W.

[NBVC]

So if we are looking at column P, and there is a date in column W, same row, then we still need to subtract 120 for column W, for the comparison? or is straight comparison to W?

If you need to subtract the specific number of days for each column from W, then select column P and edit the Red conditional format to "use a formula to determine which cells to format" and enter formula:

=IF(ISNUMBER($W5),P5<$W5-120,P5<P$5)

and click Ok, click Ok to close, and repeat for each column and subtract corresponding number of days.

else, if it is straight comparison to W date, then you can select all of Column P4:T256 range and apply "use a formula to determine which cells to format" and enter formula:

=IF(ISNUMBER($W5),P5<$W5,P5<P$5)

[dreinisch]

@NBVC, that almost works for what I have going on this spreadsheet. I'm almost wondering if changing, or eliminating, the original formulas would make more sense.

I would need the dates found in the cells in column P (to be between 100 and 110 days old from (today(C2)) to be green then (between 110 to 120 days from (today (C2)) to be orange and older than 120 days from (today (C2)) to be red.

I would need the dates found in the cells in column Q (to be between 40 and 50 days old from (today(C2)) to be green then (between 50 to 60 days from (today (C2)) to be orange and older than 60 days from (today (C2)) to be red.

I would need the dates found in the cells in column R (to be between 100 and 110 days old from (today(C2)) to be green then (between 110 to 120 days from (today (C2)) to be orange and older than 120 days from (today (C2)) to be red.

I would need the dates found in the cells in column S (to be between 40 and 50 days old from (today(C2)) to be green then (between 50 to 60 days from (today (C2)) to be orange and older than 60 days from (today (C2)) to be red.

I would need the dates found in the cells in column T (to be between 40 and 50 days old from (today(C2)) to be green then (between 50 to 60 days from (today (C2)) to be orange and older than 60 days from (today (C2)) to be red.

I am also changing the rules in column (to be between 140 and 160 days old from (today(C2)) to be green then (between 160 to 180 days from (today (C2)) to be orange and older than 180 days from (today (C2)) to be red.

All these rules I need to run in each row as they are. That is, as long as there is no date found in column W. Once a date is entered into column W.... I need the cells in that row to then draw from the date in W instead of today's date which is entered in C2.

For Example... in row 24... I enter 8/19/2011 in W24.... the cells in K24, L24, P24, Q24, R24, S24 and T24 would then have the same rules running but instead the rule would be in correlation to the new date in W24 instead of C2.

[NBVC]

Try these:

For column P:

Green:

=IF(ISNUMBER($W5),AND(P5>=$W5-110,P5<=$W5-100),AND(P5>=$C$2-110,P5<=$C$2-100)

Orange:

=IF(ISNUMBER($W5),AND(P5>=$W5-120,P5<$W5-110),AND(P5>=$C$2-120,P5<$C$2-110))

Red:

=IF(ISNUMBER($W5),P5<$W5-120,P5<$C$2-120)

Similar conditions for each of the other columns.

You may have to change some of the logicals, i.e. >=, <=, >, < to either incorporate the end date number or not..

[dreinisch]

@NBVC IT WORKED! THANK YOU! IT WORKED! IT WORKED!

I just had to change one simple thing in that.... and that was adding a ")" at the end of the
=IF(ISNUMBER($W5),AND(R5>=$W5-110,R5<=$W5-100),AND(R5>=$C$2-110,R5<=$C$2-100))

I can not thank you enough!!!!!!!!!!!