How to "Roll up" hierarchical data in indented cells in a gantt chart

davef_dci
davef_dci ✭✭
edited 06/23/22 in Formulas and Functions

Greeting, Smartsheet newby here so be gentle. I'm trying to build a "quoting tool" for our business based on a smartsheet gantt chart. Ideally I'd like to be able to enter the hours that will be worked by each of our departments and have Smartsheet calculate a proposed budget. This works pretty well with our existing workflow.


In the example shown below I have three "departments" (A, B and C) that have anticipated hours against specific tasks. I would like these hours to "roll up" into Phases - and the phases in turn to roll up into the total project. So below, under Phase 1, Department A has a total of 13 hours and under Phase 2, Department A has a total of 8 hours - yielding a total of 21 hours for the entire project.


I would need this functionality to be flexible and automatic no matter how many levels of phases, tasks and subphases are entered - so it would have to work even if I entered a few more levels of phases.

Is there a way to make this work?

Thanks and let me know if there is a better way to phrase this.


image.png


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!