conditional format (or some other method!) using icons

[bdesilva]

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

[simmo7]

Is this what you want (see file attached)?

Regards,
Maria

[bdesilva]

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.

[simmo7]

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.

[bdesilva]

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!

[zeddy]

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

[bdesilva]

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.

[sdckapr]

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

[zeddy]

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

[bdesilva]

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 $??

[zeddy]

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

[bdesilva]

Thanks. that did solve it.