Need help merging repeated sub tasks with schedule steps

sbrittin2

I am trying to figure out a way to merge high-level schedule steps with more granular tracker steps without having to build a whole new, super repetitive template.

I have a master schedule that lists out all of the tasks for a project at the chapter level. However, each chapter breaks down into multiple parts, and those parts are tracked outside the full schedule to avoid a massive amount of duplication. (Imagine each step having 10 repeating subtasks….shudder)

I use reports to indicate to the team when their tasks are ready, but they also need to see the subtasks (the parts of the chapter) that need to be finished for the overall task to be complete.

Is there any way to build those chapter parts that would repeat for each schedule step into a separate template and have them pull into a report or tracker as needed, without actually building out the schedule with all those repeating tasks?

Here's a basic example.

The schedule itself would look like what's show in Batch 2 (or the collapsed Batch 1)—the high level tasks that need to be completed for the whole chapter. Those tasks are assigned to one person as the "manager" for that task.

Here's what I'm trying to accomplish without making the schedule ridiculously repetitive and long:

This way, as other people are responsible for the sub-parts of a chapter, those lines will appear in their reports. Additionally, the "manager" can change the assignments and add individual dates to those sub-tasks without impacting the master schedule line.

I'm honestly not sure if this kind of blending is possible without a rebuild. But I would love to hear any ideas!

jmyzk_cloudsmart_jp

Hi @sbrittin2
The outline of this demo solution is as follows.

• First, create a template sheet. When the chapter number is changed, and the copy is checked in the Sheet Summary of the template sheet, rows are copied from the parent row of the chapter to the detailed schedule sheet.
• Based on the detailed sheet, create a master schedule report and determine the schedule and assignees for each primary task in the master report.
• Create reports of tasks for each assignee using the Current User filter. To understand their work's structure, display all parent rows from the detailed report. The person in charge of the major tasks can decide on the report's detailed schedule and subtask assignees. For this purpose, the detailed sheet is also shared, but each assignee's report helps them focus on checking their own tasks. (The creation of the assignee report is omitted.)

Template Sheet

In the template sheet, a column called ANC is added to understand the hierarchical structure. For example, the row corresponding to the parent row of a chapter has an ANC of 1. A sub-number is added to the task name when a chapter number is entered in the Sheet Summary. This structure is similar to the numbering in a WBS. It helps to confirm the relative positional relationships of tasks in the detailed sheet. The formulas for each column are as follows.

[ANC]=COUNT(ANCESTORS())

[Parent ID]=PARENT([Row ID]@row)

[Suffix]=IF(ANC@row > 1, RIGHT("0" + MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent ID]:[Parent ID], [Parent ID]@row)), 2))

[Task Name]=Name@row + " " + IF(ANC@row > 0, Chapter#, "") + IF(ANC@row > 1, "-", "") + Suffix@row

If you need a copy of this sheet, please contact me by email.

https://app.smartsheet.com/b/publish?EQBCT=66cda8e5343745f6812a8b739a1f49eb

Template Sheet Automation

In the automation, when the copy is checked in the Sheet Summary, the copy of the ANC 1 row is checked, and this triggers the copying of the row, including its child rows, to the detailed sheet.

[Copy]=IF(ANC@row = 1, Copy#)

Detailed schedule sheet

Each time the automation copies rows from the template sheet, the chapter is added to the detailed schedule sheet while maintaining its hierarchical structure. The sheet in the image below shows the sections where chapters 1 and 2 have been added through automation from the template.

https://app.smartsheet.com/b/publish?EQBCT=3f1d6be55a5846509318031e03ee8cee

Master Schedule

The Master Schedule is set to display the first row and rows with an ANC of 1 from the detailed schedule sheet. When the schedule and assignees are edited in this report, the detailed schedule sheet reflects the changes. Therefore, the overall supervisor can determine the rough schedule and the assignees for major tasks using this report and delegate the detailed planning to the assignees through the detailed sheet and the assignee reports created from it.

https://app.smartsheet.com/b/publish?EQBCT=1cecf7fcedfb4177882caf34642134a0