Conditional Formats using Symbols

[Azad]

Hi,
Please see attachment .xls and the Notes.
Basically If I delete the values in columns E & F I loose my Symbol formatting.
I wish to show all the 3 Symbols when Columns E & F are blank
Any help would be appreciated.

[teylyn]

hello,

actually, you don't lose your symbol formatting at all. It's just that when column E is empty, your formula delivers a "" as the end result instead of a symbol.Time to re-work your IF logic.

I'm happy to help, but from the current setup I'm not quite sure what you're after. Maybe if you could take the time to explain in words??

[Azad]

teylyn,
I do need help.What I am after is to show all the blue triangle when both the columns E & F are empty.
I have tried various if statements but no success.

[teylyn]

Let's ignore the conditional formatting for the time being. That is not the problem.

The issue is that your formula delivers "" as the result in certain conditions.

=(IF(AND($C7>G$3,$D7<=H$3),"▼",""))&IF(OR($F7<>0,$E7<>0),IF(AND($F7>G$3,$F7<=H$3),"▲",IF(AND($E7>G$3,$E7<=H$3),"◊","")))

So, please explain in simple words, what the logic is for the selection of the symbol to be displayed. I'm not sure that reverse-engineering your IF statement will do much good. Describe what and why and it can be put into a formula.

[Azad]

Ok I will try it's a bit difficult.
this is what i wish to show "▼" when E7 & F7 are blank/empty.

[Azad]

I solved by reworking the if formula.
Thanks for your help.

[Charlie_Howell]

you can also put the symbol in a different cell and refer to that cell.

[Azad]

Charlie,
Many thanks for your help.That helps.
A follow up.Please see the attached Excel file.If you look at the formula in the date Ribbon

=(IF(AND($C7>G$3,$D7<=H$3),"▼",""))&IF(OR($F7<>0,$E7<>0,$E7="",$F7=""),IF(AND($F7>G$3,$F7<=H$3),"▲",IF(AND($E7>G$3,$E7<=H$3),"◊","")))
The question is (BTW i received this file from a friend) how do I change the "▼","▲" "◊"
symbols to some other symbols?I tried using Character Map,Sybolizer,but to no avail.Any help would be appreciated.

[Marcol]

To change the symbols

Perhaps the easiest way is to put your chosen symbol in a spare cell (get it from Character Map in 2003, or Insert > Symbol in 2007)

Then copy and paste it into your formula

=IF(A2<B2,"▲","◊")

You can then delete the symbol in the spare cell.

Hope this helps

[Azad]

Hi,
Many thanks.I tried what you mentioned exactly.However when I copy and paste into the formula I don't get the symbol to display but the letter u displays.I have set the font to wingdings.Also in my file that i sent in the last post i noticed the font is callibri (in Character map there is no triangle symbol).I am using Excel 2003.

[Marcol]

The method I gave will only work for symbols.

Windings are not symbols, they are formatted text

The attached workbook might help if you are using special fonts, instead of symbols as your sample workbook suggested.

To compare the special fonts to your default font.

Format Range(I2:I256) to your choice ("Marlett, Windings, etc.)
Format Range(J2:J256) to your default font (e.g. Arial, Calibri, etc)

You will need to format your Gantt table to your special font for it to work.

Hope this helps

[Charlie_Howell]

yes i agree with Marcol....I could't even find the arrows in 2007...where did you get those things anyway...lol at any rate, you might be able to find an add-in or find these symbols on the net and save them in another workbook for later use...anyone else have any ideas as to where to get these symbols that are not wingdings or fomatted text?

[Marcol]

You can make your own symbols/fonts based on an existing Windows fonts, by using Windows own editor, Private Character Editor.

Windows Program := Run > eudcedit
Or
find Run in your Accessories folder:= Run > eudcedit