Need a formula, automation is able to do my task

Chelsea Duncan

When Assigned To changes in Task that contains "CCM Initial Checkout"(row 4) change Status in Row 3 to "Completed".

I only need it for these rows/cells, cannot have the entire column doing this.

TIA!!

Paul Newcome

Ok. Does this work?

=IF([Assigned To]4 = "", "Not Started", "Completed")

Paul Newcome

My suggestion would be a hidden date type column. Set up an automation to populate the date when "Assigned To" changes with the criteria of the task containing "CCM Initial Checkout".

Then in the Status column we would use an IF statement to say that if the hidden column is not blank (meaning it triggered the automation) then output "Complete".

=IF([Hidden Helper]@row <> "", "Complete")

Chelsea Duncan

@Paul Newcome

That works but I need the row above to change to complete. So, when CCM Initial Task has a date started, then Status on Assigned ID turns to Complete.

The statement you gave me changed CCM Initial Task to complete.

Paul Newcome

OK. Do you have multiple rows containing "CCM Initial Checkout" or is it literally just rows 3 and 4 you want this for?

Chelsea Duncan

@Paul Newcome

Just one row that contains "CCM Initial Checkout".

Also, [Status]3 need to still say "Not Started" until the formula runs.

Paul Newcome

Ok. Does this work?

=IF([Assigned To]4 = "", "Not Started", "Completed")

Chelsea Duncan

Perfect! So easy!! Thanks so much!

Chelsea

Paul Newcome

Happy to help. 👍️