Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    168
    Thanks
    2
    Thanked 0 Times in 0 Posts

    conditional format (or some other method!) using icons

    In MS Project I create a dashboard like column that puts a red, yellow or green icon in the cell depending on the status of the task. Can I do this in Excel. I've tried finding some way to use conditional formatting but no luck. For example.

    Col1 = where I want to put the icon
    Col2 = has budget estimate
    Col3 = has budget used

    Make col1 have a red icon if Col3 > Col2

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    73
    Thanks
    7
    Thanked 2 Times in 2 Posts

    Conditional Format

    Is this what you want (see file attached)?

    Regards,
    Maria
    Attached Files Attached Files
    Maria
    Simmo7
    Victoria, Australia

  4. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    168
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Maria,
    Well I tried it. I have two problems. (1) I can't get it to display the wingding font you have showing. AAMOF when I chose font they are all grey and I can't choose one. (2) I'm hoping to use graphics (or text) for three choices, red, green, yellow.

  5. #4
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    73
    Thanks
    7
    Thanked 2 Times in 2 Posts

    conditional formatting

    I don't know why you can't change the font, perhaps you don't have wingdings installed?? I have changed to Arial Font, hopefully this is now OK for you.

    Also I missed one step, first you select the range that needs the conditional format, then you Choose Use Formula to determine Format and enter the formulae that determines the format, then click on format and choose the relevant format for that formula. for example = $C2>$B2 then format cell fill = Red. = $B2>$C2 format cell fill = Green. = $b2=$c2 format cell fill = Yellow.
    Attached Files Attached Files
    Maria
    Simmo7
    Victoria, Australia

  6. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    168
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks. I was trying to format to wingdings as part of the conditional formatting. That works now. Doesn't do what I wanted it to do yet, but it's enough for now!
    Thanks!

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,472
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi

    In my attached workbook, I have formatted cells in column [A] with Black fill, and Wingdings font.
    I have put conditional formatting in these cells, to show text as colour red, green or yellow (depending on value in col [D] compared with value in col [C]
    I have defined named cells to store a particular Symbol character (which could be changed to different sysmbols if required).
    I have used the named cell in my formula in col [A] to place the required symbol character (depending on value in col [D] etc)

    Does this help?

    zeddy
    Attached Files Attached Files

  8. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    168
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thanks. that worked, except for me the conditional formatting is not 'taking'. seems silly, but I'm getting NO conditional formatting on those cells. I've attached a couple of screen shots in case they'll help.
    Attached Images Attached Images

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,162
    Thanks
    14
    Thanked 317 Times in 311 Posts
    From the screen shot, the formulas are looking to see if a TEXT value is true, it will never be...

    You must remove the quotes from before and after teh formula to have formulas actually compare something...

    Steve

  10. #9
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,472
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi

    Even removing the quotes will not work as the formula doesn't make sense.

    I think the formula should be:
    =$A$4<($F$4*.75)

    zeddy

  11. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    168
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I had tried that as it looked incorrect to me, but then it returned an error.
    Tried again this morning and it helped. Also Zeddy's comment helped, although AG and AF are correct I somehow had split the letters. NOW, everything comes up green, there are no reds and some should be! But I'm closer. A little more detail might help.

    I have a formula in the cell =IF($B12="","",IF($AG12>$AF12,Red,IF($AG12<($AF12* 0.75),Green, Yellow))) and the result is the Red one so I know that $AG12>$AF12 is true. In the conditional formatting I have =$AG$4<($AF$4*0.75) as the first rule which colors the cell green, and =$AG$4>$AF$4 in the second rule which colors the cell red. The cell is being colored green. The second rule matches the cell formula so I figured it'd color it red. I do not have stop if true checked either.

    The value in AG12 = 13000, AF12 = 6000

    Should the cond format not have the $??

  12. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,472
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi

    If you are checking the contents for values in row 12 (The value in AG12 = 13000, AF12 = 6000) then the conditional format rule should refer to this row as well, so you are right you should not be using absolute values i.e. using $ for the row number.

    The way I do conditional formats in Excel2007 is to set the rules up for one cell (without using $ for the row), and then when this is tested OK for that cell, I copy-and-paste formats down the relevant column.

    zeddy

  13. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    168
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks. that did solve it.

Posting Permissions

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