Conditional formatting with icon sets

[malbar]

Hi,

I have attached a Yearly Goals template that I have put in place to rate the players that I coach with regards to their progress made/targtes hit.

I wanted to use the icon sets available in excel to give the worksheet a professional look. I could not work out how to use the icon set where text was inserted so I used the following formula -

=IF(H11="R",3,IF(H11="A",2,IF(H11="G",1,"")))

and had the relevant icon appear in "H12", I then formatted the "H11" cell white font so it look liked when R,A or G was inserted the said icon would appear.

The problem I keep running into is that because I have made the text column narrow and white font (as I really wanted it so it looks like just an icon appears) coaches that are filling the template in will automatically delete the icon if they have made an errror when inputting their rating!! This then deletes the formula, which in turn means the conditional formatting is not applied.

To cut my above ramblings short lol!! I was hoping that there was anyway I could use text for icon sets to eliminate the need for using a formula!!

Thank you

Col

[6StringJazzer]

Icon sets use numbers (as far as I can tell). What you did is a good workaround. To solve the problem of data entry, unlock the cells that you want the coaches to be able to change then protect the sheet.

[Andy Pope]

Does this work for you.

First I created a list of Red,AmberGreen using the values -1,0,1. To this I applied a custom number format. "Green";"Red";"Amber"

The data validation in H11:H16 uses the list, which I called, RAG_LIST.

The CF icon set for H11:H16 uses the traffic lights based on the values
>=1
<1 >=0
<0

[malbar]

Does this work for you.

First I created a list of Red,AmberGreen using the values -1,0,1. To this I applied a custom number format. "Green";"Red";"Amber"

The data validation in H11:H16 uses the list, which I called, RAG_LIST.

The CF icon set for H11:H16 uses the traffic lights based on the values
>=1
<1 >=0
<0

Could I ask the procedure you used to do the number formatting?? and what is column I for?? Could I also hide the column with RED,GREEN AMBER in it and the data validation still work??

Thanks
Colin

[Andy Pope]

Yes you can hide the list that is in column P.

To apply custom number select the cells and press CTRL+1
Number tab. Select custom and enter new format mask.

[malbar]

Yes you can hide the list that is in column P.

To apply custom number select the cells and press CTRL+1
Number tab. Select custom and enter new format mask.

Many thanks for your help the worksheet looks great!!

Just out of curiosity on the number format part. -1,0,1 why was the custom format in this order "Green";"Red";"Amber" I thought red being -1 would either be 1st or last not in the middle??

Cheers
Col

[Andy Pope]

Have a read up on the number format construct.
http://office.microsoft.com/en-gb/he...090.aspx?CTT=1