Excel traffic lights based on 4 different text

[RELU]

Good day

Struggling to find best solution to have traffic lights for 4 different text in excel, using format cells custom options, having scroll down options using data validation list

have set custom format cells options set based below
"APPROVED";"PENDING1";"PENDING2", based below numerical values the scrolling options comes as below

-1​	PENDING 1	Red light
0​	PENDING 2	Red light
1​	APPROVED	Green light
	PENDING 3	Red light

What I am struggling is how to add Pending 3 in red, as custom options allows you to use only 3 conditions, not 4
Any suggestions how to fix it using the same logic?

 

[jasonb75]

Try using 1, 2 and 3 for pending, -1 for approved, with this custom format

"Pending "#;"Approved";;@

and the icon set formatted as:-

- Red:- >=1
- Yellow:- <1 and >=0
-Green:- <0

 

[RELU]

Great thank you this works like a O'clock. Is there a way to add text to these pending, instead of having numbers 1,2,3.
Lets say
Pending SL (previously pending 1),
Pending RL (previously pending 2),
Pending SCM (previously pending 3)

 

[jasonb75]

Not if you want to keep 'Approved' in the list as well. For actual text strings based on the numeric value, the limit is 3 different strings.
The way that I suggested only works because it is set so that all positive numbers show "Pending", then the number selected is added to the end of it by use of the # symbol.

The only other option would be to use proper text in the dropdown instead of the custom formatting, then have the traffic lights in a separate column with a simple formula to convert the dropdown text back to a number.

 

[RELU]

Thank you, I thought so, was just trying to find easiest way not to change all spreadsheet, but I guess not option. thank you again was very helpful

 

[jasonb75]

If it helps, I have an untested idea that might work to show the text strings (Pending SL, etc.) in the cell with conditional formatting, but I believe that it would still show the original version (Pending1) in the dropdown.

 

[RELU]

hi Jason, tried as well with conditional formatting, did not give any result I wanted. In the end just added separate column and problem solved. Thanks again for the feedback and help.