Move Row Automation with Child Tasks

Kayla K

Hello,

I'm using a sheet to track all projects for this year (Parent), their major tasks (child), and then the subtasks (child of task). Each row has a checkbox to click when completed. I want to set up a Move Rows automation for when the project is checked complete, that parent row & all its child rows get moved to a sheet for completed projects.

When I tried to set it up I could only get the parent row to move, leaving all the child rows behind and messing up my sheet. Is there an easy way to do this?

Thanks in advance for any advice.

jmyzk_cloudsmart_jp

Hi @Kayla K

"Move Row Automation with Child Tasks" is a practical good question!

I first determined the top ancestor's Row ID using this formula.

[Top] =IFERROR(INDEX(ANCESTORS([Row No]@row), 1), [Row No]@row)

The formula for Row No is as follows: (Since the [Row ID]s do not match the Row number when you move the rows by automation or when you modify the project sheet to change the order of tasks, for example, you need this [Row No] .

[Row No] =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Then, using the [Row N]o as the INDEX function's row argument, I got the [Top Completed?]'s value by this formula.

[Top Completed?] =INDEX([Completed?]:[Completed?], Top@row)

Using this [Top Completed?] as a trigger, I moved rows to the Project Archive Sheet.

https://app.smartsheet.com/b/publish?EQBCT=9d5234a489b64cb9a65a88b2f2c3c21f