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.



Answers

  • Perhaps an update on this. I've learned how to Sum(children) which does what I want but it would be very laborious to add this manually anytime I add an indent. In the examples below Colum 10, cell 1 has a Sum(children) and is correctly calculating the sum of all the numbers below. However in task 5 I entered Hello and then promoted "test" below that. I'd like "Hello" in Column 10, cell 5 to automatically roll up all the tasks below it when I promote tasks below it. This works in Project for me - is there a way to do this in Smartsheet?


    Thanks!




  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @davef_dci

    I hope you're well and safe!

    Unfortunately, it's not possible now, but it's an excellent idea!

    Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

    Here's a possible workaround or workarounds

    • You'd have to add a so-called helper column because you can't have a column formula and be able to add information in the same column manually.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!