+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting icons help

  1. #1
    Registered User
    Join Date
    12-20-2010
    Location
    Saskatoon, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    3

    Conditional formatting icons help

    Hi, I am trying to make a visual skills matrix spreadsheet. I have formatted the cells to display the 5 graduated black and white icons that show from an empty circle to a filled in circle. These are set from 0 experience, to 100 experience(using numbers as opposed to percent). This works fine. What I need is a way to get a green filled in circle displayed for those people that can train, so basically have more than "100". I can enter anything for this to happen, but cannot figure out how to do this. Does anyone have a solution for me? I can provide more information if this is not clear enough.
    Thanks, Greg
    Last edited by bevelman; 12-23-2010 at 05:18 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting icons help

    You will need to display the icons in a separate cell from the values.
    In the cells where the CF Icons would be shown use a formula to get the value to use or the characters 'n'. Format the cells as Webding font size 11 and green font colour.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    12-20-2010
    Location
    Saskatoon, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional formatting icons help

    Hi Andy,
    Thanks for the quick reply. I can sort of see where you are going with that, but it seems pretty intensive for what I am trying to do.(six lines of formulas per cell?) I am not that familiar with excel, and appreciate the help. I will attach the file I am trying to use, so you can see where I am coming from. the green dots in that file are unfortunately attached to the cell position, so do not sort, and that is my undoing.
    Greg
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting icons help

    You may have to compromise then.

    How about applying the 5 Quarter icons as normal then additional fill the cell green if the value is greater than 100.

  5. #5
    Registered User
    Join Date
    12-20-2010
    Location
    Saskatoon, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Conditional formatting icons help

    That is the issue I am running into, and why I am asking for help. The conditional formatting only allows <=, or >= as parameters. Therefore if I use a set of icons with a green dot, and the 5 quarter set, they conflict. If I set 5 quarter first, then the green set never applies, as the last parameter of the 5 quarter is >= 100. If I set the green set first, I get that icon sets icon for values below whatever value I set for the green dot... when what I want is the 5 quarter set for the lesser values.
    I have tried using parameters other than "numbers" for the green set, but without success, but as I said, I am not overly conversant with excel.
    Greg

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting icons help

    And the help is, that if you want 5 quarters CF then you are stuck. As any value will meet with at least 1 of the conditions. As it is the nature of the conditions to apply to all values.

    Once alternative would be to use Coloured dots for all values rather than quarters.

    Another would be to use a font that supports little pie charts.
    http://www.fontspace.com/curtis-clar...harts-for-maps

    Of course this will require any other user of your workbook to also have the font.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1