Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •