In colmun S I've got cells with values likes these in them:
98 (+2)
103 (-8)
112 (0)
I want to use conditional formatting to choose from a set of 3 icons based on the number on the left,
>109 = green icon
100-109 = yellow icon
<100 = red icon
Against the top / green icon I set Type to 'Formula', and in valyue I put
=value(left(S2,find(" ",s2)))>109
Against the middle / yellow icon I set Type to 'Formula', and in valyue I put
=AND(VALUE(LEFT(S2,FIND(" ",S2)))>=100,VALUE(LEFT(S2,FIND(" ",S2)))<=109)
...but Excel said "You cannot use relative references..."
Anybody know how to work around this?
Many thanks
98 (+2)
103 (-8)
112 (0)
I want to use conditional formatting to choose from a set of 3 icons based on the number on the left,
>109 = green icon
100-109 = yellow icon
<100 = red icon
Against the top / green icon I set Type to 'Formula', and in valyue I put
=value(left(S2,find(" ",s2)))>109
Against the middle / yellow icon I set Type to 'Formula', and in valyue I put
=AND(VALUE(LEFT(S2,FIND(" ",S2)))>=100,VALUE(LEFT(S2,FIND(" ",S2)))<=109)
...but Excel said "You cannot use relative references..."
Anybody know how to work around this?
Many thanks
Last edited: