How to automatically change a status cell color due to other columns being changed

Gary Collins

I want to have a status column that changes color due to other columns being ticked off, I use columns (symbols) for tracking progress of a task and want to be able to have a status column that changes color as each task column is updated from a red X symbol to a green tick symbol. So when first column changes to green tick updates status column to yellow, second column changes to green tick updates status column to brown, third column changes to green tick updates status column to orange and so on. I have tried with conditional formatting but it is not updating (keeping first conditional format as is still active), is there an automation that will do this?

Hudson_Miller

@Gary Collins

Recommend you use a helper column with a formula that evaluates the task columns and produces an integer value. Then you can base the status column and conditional formatting on that single helper column.

Without working the formula, it would go something like this =COUNTIF([Task1]@row:[Task3]@row, "Yes")

Gary Collins

I think I worked this out, by using a combination of automations for each change and then conditional formatting once the change has been seen.

This is how I did it:

1.Status column using dropdown with the different column names that will be checked off.

2. Automation to change the value of the Status column as the task columns are changed from red X to green Tick.

3. Conditional formatting to change when different status name is used.

Marcela

Hello @Gary Collins,

Thank you for sharing your solution!