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


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
    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!



  • 22Wordsmith
    22Wordsmith ✭✭✭

    Many thanks (again)!