Mixing icon sets in conditional formatting and only using one icon from set 2

[IconSets]

So I created a way to show red, yellow, and green based on an if formula and conditional formatting.


=IF((B3="Red"),1,IF((B3="yellow"),2,IF((B3="green"),3,"N/A")))


So if I put Red, the value is 1 and then I changed the cell color white so that all users would see is the icon and the icon would be based on a color I wrote in cell B3.


However, what I want to do is add one additional conditional formatting icon set, but only use one icon from that set. I want to use Red to Black and set it up so that I can get only the gray... e.g if I type "N/A" I want just the gray icon to show, but I still want red, yellow, and green to appear if the value is 1, 2, or 3.


Does anyone know how to merge two different icon sets so that they don't overlap and so that I can have only the grey icon appear when I type N/A, otherwise keep the red green yellow schema?

 

[Hui]

Iconsets


In Excel 2010 there is a 4 traffic light set with Red, Yellow, Green and Black


alternatively just use Conditional formats to set the background colors


As yet we haven't been given the facility to edit Icon sets.

 

[IconSets]

So I don't have Excel 2010 yet, but I don't want Red, Yellow, Green, Black. I want Red, Yellow, Green, Grey. I also don't want to set the background colors, I want to use the grey circle from the Red to Black icon set. Any idea how to accomplish this exact task?

 

[Hui]

Ok


Lets say you have a value in A2


In D2 or where ever you want your Icons put the following formula

=CHOOSE(A2,2,1,0,"a")


Apply a Custom Number Format (Ctrl 1)

and enter and apply the following custom Number format

[Red][=2]n;[Yellow][=1]n;[Green]n;n


Now Change the default Text Color of the cell to Grey

Change the cells Font to Web Ding


Voila


How does this work?


The custom number format does 2 things

It applies colors to all the ranges =2, =1 and 0

It replaces the cells display value with "n"


N is the Web Ding Value for a Large Filled Circle


The default Color of Grey is used for the Text value which is the 4th value in the custom format which is also n when the value is 4


You Can change the Text value in

[Red][=2]n;[Yellow][=1]n;[Green]n;n

to

[Red][=2]P;[Yellow][=1]P;[Green]P;P

to give different characters

eg: change to a "P" and use Wingdings 2 Font for a nice Tick mark

 

[IconSets]

I really just want two instances of conditional formatting, your solution is VERY clever and quite good, but I want to stick with Excel's built in custom designed conditional formatting icons.

 

[Hui]

Customisation of Iconsets as you require is not available in Excel 2007 or 2010


The process I described above works a treat and will solve your problem

 

[IconSets]

Hui,


I do not need to customize the icon sets, I like the built-in ones. All I want to do is have Excel leverage two different conditional formatting icon sets in one cell.


So basically if the value is x,y,z use the red, green, yellow stop light. If the condition is a then use the red to black icon set and only call on the grey icon. This is possible in two different cells, but I just want to be able to do it in once cell without the use of webdings or anything other than Excel's icon set(s).

 

[dcroset]

Excellent. Thanks Hui.