How to create Traffic lights from text if cell already has a formula in it

[Sly1980]

Hi

I hope that this question is clear enough - I have a working document that uses 'R', 'Y', 'G' as potential inputs. This could change daily as team members update it. I've created a summary sheet for the data but the data on the summary sheet is transposed using the an index formula (data that is inputted vertically is shown horizontally in the summary sheet).
I would like to show traffic lights on the summary sheet instead of the letters R, Y, G but excel only supports numerical values for icon sets. There's is quite a bit of data that needs to be pulled across so I'd like to do this without creating an additional column for each category if possible. Please advise if this is possible.
*Bonus - is there any way of adding an additional traffic light to depict if the cell is not completed?

 

[GraH]

How do you define "R, Y, G" or the traffic lights?
I believe something like this could work?

* "Empty" is not entered, it was just to show ""
Set-up looks like this:

Book1
	C	D	E	F	G	H
2	Value returned (entered manually or by formula)					CF
3	Bad					l
4	Okay					l
5	Could be Better					l
6	Empty					l
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:H6	Expression	=$C3="Could Be Better"	text	NO
H3:H6	Expression	=$C3="Okay"	text	NO
H3:H6	Expression	=$C3="Bad"	text	NO

 

[Sly1980]

How do you define "R, Y, G" or the traffic lights?
I believe something like this could work?
View attachment 10907
Set-up looks like this:

Book1
	C	D	E	F	G	H
2	Value returned (entered manually or by formula)					CF
3	Bad					l
4	Okay					l
5	Could be Better					l
6	Empty					l
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:H6	Expression	=$C3="Could Be Better"	text	NO
H3:H6	Expression	=$C3="Okay"	text	NO
H3:H6	Expression	=$C3="Bad"	text	NO

Hi
Thank you for the reply. I define R,Y,G as the status of a particular item, i.e., if there are no issues, G, if there are some issues, Y, if issues are critical then R.
I really would like use the traffic lights as this is the generally accepted standard for reporting - so even if its just one circle with the correct colour that would be fine. It would also be ok to leave cells that have no info as blank.

 

[GraH]

So you might use your if logic (using countifs?) and then attribute a number where 0 = blank, 1 = G , 2= Y and 3 = R. Finally apply the CF on that value to create the traffic light.
Something like = IF (Countifs (Critical, "Yes"), 3, IF (countifs ( issues, "<>")>Treshold, 2, ...
Not sure if that makes sense with your dataset.

 

[Sly1980]

So you might use your if logic (using countifs?) and then attribute a number where 0 = blank, 1 = G , 2= Y and 3 = R. Finally apply the CF on that value to create the traffic light.
Something like = IF (Countifs (Critical, "Yes"), 3, IF (countifs ( issues, "<>")>Treshold, 2, ...
Not sure if that makes sense with your dataset.

Hi
Thank you for the response - I eventually went the long way of creating new columns with conditional formatting using webdings and colour coding them accordingly. was quite painful but it seems to have worked! thanks again for your time and advice.