first
When managing projects with Smartsheet, there are cases where you want to manage parent tasks and child tasks, and then move the parent tasks and child tasks together to a separate sheet when the project is complete.
In this post, we will introduce the detailed steps and Japanese translation based on jmyzk_cloudsmart_jp's answer to Kayla K's question about ``Moving row automation using child tasks'' on the Smartsheet forum.
Question summary
- Manage projects (parent tasks), main tasks (child tasks), and subtasks (children of tasks) on sheets
- Each row has a checkbox to click on completion
- When a project is completed, I want the parent row and all its child rows to be automatically moved to the sheet of the completed project.
Answer summary
- Determine top ancestor row ID
- [Start] =IFERROR(INDEX(ANCESTORS([row number]@row), 1), [row number]@row)
- calculate line number
- [Row number] =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
- Get top completion status
- [Complete?] =INDEX([Complete?]:[Complete?], Top@row)
- Move row to project archive sheet using above formula as trigger
- For detailed steps, see jmyzk_cloudsmart_jp's answer (https://app.smartsheet.com/b/publish?EQBCT=9d5234a489b64cb9a65a88b2f2c3c21f) Please refer to.
Summary
Using Smartsheet's automation features and the above formula, you can automate the movement of rows containing child tasks.
Key Points
- Identify the row ID of the top ancestor
- calculate line number
- Get top level completion status
- Move the line using the above as a trigger
Understanding these points will enable more efficient project management.
We hope that this article will be useful for your use of Smartsheet.
Translation of correspondence
Question
Kayla K. ✭✭
Hello,
I use sheets to keep track of all my projects this year (the parent), their main tasks (the children), and their subtasks (the children of the tasks). Each row has a checkbox to click when completed. I would like to set up a row move automation so that when a project is checked for completion, its parent row and all its child rows are moved to the completed project's sheet.
When I tried to set this up, I was only able to move the parent row, leaving all the child rows behind, making the sheet a mess. Is there an easy way to do this?
Thank you for your advice.
answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
24/01/24 Edited on 2024/01/24
Hello@Kayla K
"Move row automation using child tasks" is a good practical question.
First, we used this formula to determine the row ID of the top ancestor.
[Start] =IFERROR(INDEX(ANCESTORS([row number]@row), 1), [row number]@row)
The formula for calculating the line number is: (This is required because the Row ID does not match the row number, such as when moving rows in automation or changing the order of tasks by changing the project sheet. Row Number.
[Row number] =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
I then used [Row N]o as the row argument of the INDEX function and got the value of [Top Completed?] with the following formula:
[Complete?] =INDEX([Complete?]:[Complete?], Top@row)
This [Top Completed?] was used as a trigger to move the row to the project archive sheet.
https://app.smartsheet.com/b/publish?EQBCT=9d5234a489b64cb9a65a88b2f2c3c21f
