conditional formatting result of formula

[Kim B]

Hello. I want to apply a conditional formatting icon but the tricky part is the cell that receives the formatting isn't the result of the formula.

Excel Workbook
	C	D	E
4	1/1/1991	26	40
Sheet1

the difference result if 40-26 is 14 the conditional formatting needs to be applied against the result of 14 being less than, greater than, or equal to a value. So if the result is less than 5 then i would want a yellow icon.

Not sure it can be done without a result column, but thought I would give it a shot

thanks in advance

 

[shg]

Kim, you have absolute references in all those formulas, so the formatting in E4:10 depends only on what's in F4 and D4.

Is this the result you want?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

29​

[/td][td="bgcolor:#FFC000"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

30​

[/td][td="bgcolor:#FFC000"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

31​

[/td][td="bgcolor:#92D050"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

32​

[/td][td="bgcolor:#92D050"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

33​

[/td][td="bgcolor:#92D050"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

34​

[/td][td="bgcolor:#00B0F0"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

35​

[/td][td="bgcolor:#00B0F0"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

36​

[/td][td="bgcolor:#00B0F0"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

37​

[/td][td="bgcolor:#00B0F0"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

38​

[/td][td="bgcolor:#00B0F0"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

39​

[/td][td="bgcolor:#00B0F0"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

40​

[/td][td="bgcolor:#FFFF00"][/td][td]

40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

41​

[/td][td="bgcolor:#FFFF00"][/td][td]

40​

[/td][/tr]
[/table]

If so, select E4:Ewhatever,

=F4-D4<15 format orange
=F4-D4<10 format green, stop if true
=F4-D4<7 format blue, stop if true
=F4-D4<1 format blue, yellow if true

Each added condition pushes the others down, so they will appear in reverse order.

 

[shg]

=F4-D4<1 format blue, yellow if true

~ stop if true

 

[Kim B]

Here is the real legend, the example above was trying to just get something to work

[TABLE="width: 197"]
<colgroup><col></colgroup><tbody>[TR]
[TD]5 years or less - yellow[/TD]
[/TR]
[TR]
[TD]due for replacement - red[/TD]
[/TR]
[TR]
[TD]7 years or less - blue[/TD]
[/TR]
[TR]
[TD]10 years or less - green[/TD]
[/TR]
[TR]
[TD]15 years or less - orange[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kim B]

got it to work, here is the order in case anyone is following this

red is < 0
yellow is <5
green is <10
blue is < 7
orange is < 15

all stop if true checked. And thank you to shg for all the help!!

 

[shg]

You're welcome, good job sorting it out, Kim.