another conditional format issue

[RDOREMUS]

hello all,

i have a conditional format question about a simple spreadsheet.

what i have is 4 columns of expiring dates.

C D E I

1-jan-11 2-feb-12 3-mar-12 x


well what im having a problem with is that. . C:E is conditionally formatted off of a seperate column A, which is working the way i need it to. what i am looking for is for is a formula for when i have an X in column (I) that it makes C:E go to no color and if the X is taken away it goes back to its original conditional formatting based off of column A. Will i have to write a macro or is there a simpler way, because i have been trying all different types of formulas and have had no success at all. Thank you for your response.

 

[jasonb75]

Just needs a slight change to your existing formula, something like

=AND(existing formula,$I2<>"x")

 

[RDOREMUS]

would i put that in column C, as just the format, or put that into the conditional formatting formula section

 

[jasonb75]

Into the conditional formatting formula for all columns that it should apply to.

 

[RDOREMUS]

alright imma try that. thank you for your help jason.

 

[RDOREMUS]

jason,

i tried entering in your formula, it works for the first Cell but the other two cells wont format. any suggestions?

 

[jasonb75]

Did you edit each column C,D,E and F individualy?

Without knowing your existing conditional format formula, I could be wrong, but I would guess that each column has different criteria.

 

[RDOREMUS]

pretty much its like this...

columns

B C D E F

1-jan-12 31-jan-12 1-mar-12 31-mar-12 X


C- B+30days
D- B+60days
E- B+90days

what i have it doing is if today is 15 days or before B then C turns Green, if today is 15 days or equal to B then it turns red and if today is 1 day or more past B then it turns red. but if there is an X in F then all conditionally formatting goes back to clear. i hope that makes sense.

 

[jasonb75]

So my previous guess was correct then, you need to edit the conditional format formula for each column individualy using the suggestion in post #2

 

[RDOREMUS]

yea i think your right, i just think my one formula is messing things up. . .would this condition for yellow be correct?

=AND(C1-TODAY()<15,C1-TODAY()>0) - YELLOW

like if i want it for when its 15 days to 1 day before the date in C1 to turn yellow