Traffic Light based on multiple-select drop-down in next column

22Wordsmith

Hi,

I would like to automate my traffic light (here called Overall Assessment) based on the count of options selected in Potential Impediments. It is possible to select 0-5 items at any one time.

Would the best option be to have a hidden column that counts the selection and use that as a reference point for the traffic lights?

Ric T

Hi @22Wordsmith,

Haha just saw your other question and can see what you're trying to achieve here :) you're absolutely right about the hidden / helper columns.

1) I would create a helper column (e.g. "PI Helper") on the right of overall assessment, apply the formula to all rows, and enter: =COUNTM([Potential Impediments]@row)

2) I would then apply the formula below to convert the Overall Assessment column into a traffic light symbol indicator, something like this:

=IF([PI Helper]@row = 1, "Green", IF(AND([PI Helper]@row >= 2, [PI Helper]@row <= 3), "Yellow", IF(AND(PI Helper]@row >= 4, [PI Helper]@row <= 5), "Red", "")))

The formula is written if PI helper is 1 (green symbol), if PI helper is 2-3 (yellow symbol), and PI helper is 4-5 (red symbol), feel free to modify to suit your needs!

Cheers,

Ric

22Wordsmith

Many thanks (again)!