I created a master sheet that will list all projects for a department. We will also have a planning sheet for each project in the master sheet, created by copying one of four templates (based on the project type).
Each of the four templates will have columns set up and a hierarchy of rows based on phases, workstreams, deliverables, and tasks. It will track the number of tasks completed (and total number of tasks) and % of tasks completed and # of tasks overdue. These data points will roll up to master sheet via cell links. The planning sheet would also have cell-reference formulas that pull data from other sheets in our ecosystem.
I know there is not a native way in Smartsheet to create the project planning sheet (when a new row is added, using a template, based on the project type). It wouldn't take someone but a few minutes to do this, and give the planning sheet a specific name based on our agreed-upon naming convention.
My question is about setting up the cell links and modifying any cell-reference formulas, in the planning sheet and master sheet.
Will the cell reference formulas (e.g., pull program information from a program master sheet, based on the program code entered manually in the planning sheet) copy over from the template?
I can't think of how the cell links would be created in the master sheet to the new planning sheet, other than manually. In the new project row in the master sheet, the individual would have to go to the column for, say, % of Tasks Completed, and manually add the cell link to the new planning sheet?
Before I begin setting these up (creating the master sheet, templates, and "test" planning sheet), I wanted to see if anyone had recommendations on how to do this in a way that requires the least amount of manual work.
Thanks!
