Stacked Conditional Formatting based on values from an IF statement?

[nscarritt]

sample.jpg

I am wondering how possible it is to do some sort of a conditionally formatted status indicator, such that "If Actual < Goal, Display <RED FILL or some icon>, Display Green Icon" . I have played with conditional formatting but it seems that it will only format a cell relative to some defined constant.

Maybe there is an entirely better way to do this that I'm not aware of.. what do you all think as a solution? Any help would be a life saver!

[FDibbins]

Hi and welcome to the forum

Take a look at using "use formule"

=$D2<$C2 format fill read
new rule (use formula)...
=$D2>$C2 format fill green

If you want the entire row to be highlighted, select the entire range 1st, then apply the rules

[nscarritt]

Thank you @FDibbins. I guess I am still a little confused where you find that feature? Also, you're saying to do a conditional format with multiple rules if I'm reading correctly, right?

[FDibbins]

Yes, that is what Im saying. Your profile indicates 2010, so you can use many rules

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$D2<$C2

Repeat 3 & 4 for the other new rule

[nscarritt]

Thank you so much for the clarifications... I'll try it out and return with reps when i get it working. Thank you!

[nscarritt]

One last follow up question.. Say I have 7 rows in a template, and want people to be able to enter 3-7 of these metrics. When I have the >= operator, it formats the blank columns. Is there any way to add a qualifier for "If no data then no format" ?

[FDibbins]

Maybe this...
=and(D2<>"",$D2<$C2)
I selected D2, figuring you will always have a budget, but might not yet have an actual