Unable to change cell with using an automation

christine_ford2003

I am trying to create an automation that changes a cell when another cell changes.

For example: When status column field changes to On Hold, the Health column field is to change to the yellow circle (symbols option). It does not give me the option to select the Health field. Does it have to do with being a symbol?

David Tutwiler

That's correct, that is currently not available through Automation.

However, you could do that with a column formula. If you put something like the following in the Health field, it should work:

=IF(Status@row = "On Hold", "Yellow")

christine_ford2003

Great suggestion, thank you! There are 2 statuses that change the health to Yellow.

On Hold, Blocked

Can you help with the formula to include these two?

David Tutwiler

Of course:

=IF(OR(Status@row = "On Hold", Status@row = "Blocked"), "Yellow")

christine_ford2003

again... thank you. Works great.

David Tutwiler

Awesome. Glad to hear it's working

christine_ford2003

@David Tutwiler

I need your expertise one more time. What is the formula to include other statuses that are mapped to each symbol.

For example: In Progress = Green // Blocked = Yellow // Complete = Blue

David Tutwiler

You would treat those statements as the False section of the original IF statement. Essentially, the first formula I wrote was for IF the statement was TRUE. If not, then we would add the additional conditions. Something like this should get you started:

=IF(OR(Status@row = "On Hold", Status@row = "Blocked"), "Yellow", IF(Status@row = "In Progress", "Green", IF(Status@row = "Blocked", "Yellow", IF(Status@row = "Complete", "Blue"))))

PDunn

this really doesn't work if you need create a column formula on a sheet with hundreds of projects in different priorities and health status', it wipes out all other health colors once you convert to column formula. So you end up having to write a big long formula to automate the entire column based on each Priority and Health. Would be so much easier to include health in the automation options.