Conditional formatting / icon sets/ parameters based on text from another cell.

motowndown22

New member
Joined
Oct 20, 2014
Messages
1
Reaction score
0
Points
0
Hello,

I am trying to use conditional formatting with the stop light icon sets. (Spreadsheet attached)

What I really need is for the parameters to be set based on the text from another cell.


Ex.
C3 says "Other", this could also say HC or MF.


Opportunity Analysis (OAS)
Name YRC
Vertical HC/MF/OTHER Other
Actual Total number of locations 400
Number of "Open Market" locations 300
Number of "Franchised" locations 100
Shared Savings Fee % 45%
Annual Waste Spend - Total 3,800,000
Annual Waste Spend - Open Market 2,850,000
Estimate % savings 20%
$ savings (open market) 570,000
Cass annual fee 256,500
Cass net monthly fee 21,375
Fee per location per month 71



MF Stop Light
Est. Fee per location per month > 85
Proceed with caution 75 - 84
Stop - See VP of Sales < 75


HC Stop Light
Est. Fee per location per month > 125
Proceed with caution 115 - 124
Stop - See VP of Sales < 115


Other Stop Light
Est. Fee per location per month > 85
Proceed with caution 75 - 84
Stop - See VP of Sales < 75


Based on the parameters above, how can I use conditional formatting in C14 to be dependent on which word has been typed in C3?
C14 currently holds the value 71 that was derived from a sum function =SUM(C12/C5)/12. C14 currently has a red background with a red light, but is not using C3 to base its rules off of. The rule is only coming from =$C$14. How can I get the parameters to apply "if C3 is this word" "then these rules would apply to C14"?

Thank you for any knowledge you can spare.
 

Attachments

  • OAS.xlsx
    12.2 KB · Views: 126
What you are asking is
"is it possible to have conditional formatting to display an icon set using a formula"?

As far as I can tell no, either using icon set or formula (there is no format style=icon with a formula)

so either format with fill colour

using formula rule similar to
=AND((C3="Other"),C14<75)
=AND((C3="Other"),C14>85)

OR split your answer and icon into two cells and where the icon goes calculate (-1,0,1) depending on the rules

Perhaps also take advantage that MF and Other are the same rules so only test for HC
=if(C3=H3,if(c14<115,-1,if(c14>125,1,0)),if(c14<75,-1,if(c14>85,1,0)) (hope I got the brackets and commas right)
 
Back
Top