Need a formula to copy contact name from cell when criteria is met in another cell

Laura M.

When the "Assigned To" (Assignee 1) in row 1 checks "Completed" copy "Successor" (Successor 1) in row 1 to "Assigned To" in row 2. Then I will create an automation to notify the new "Assigned to" in row 2 that the task is now assigned to them. The end goal is to create an automated process that moves through the project tasks without project manager involvement. Our team tracks their work on individualized sheets pulling tasks assigned to them from all active sheets. So, I'm trying to avoid tasks populating their individual sheets before they can work on them.

Screenshot 2024-06-21 at 1.29.32 PM.png

Laura M.

Here is image of Screenshot included in previous comment.

Paul Newcome

My suggestion would be to start with a sheet summary contact type field where you can enter the "starting assigned to" that goes in row 1.

Then you would insert an auto-number column (called "Auto" in this example).

Next a text/number column (called "Row" in this example) wiht the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Finally the column formula for the [Assigned To] column would be

=IFERROR(INDEX(Successor:Successor, Row@row - 1), [Starting Assigned To]#)