Adding column of summary date values

Lindsay AR
Lindsay AR ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I have a "Date Completed" column in my list that I would like to roll up like Start and Due Date do with sub-tasks in the hierarchy. I have been unable to figure it out with formulas. So, I am looking for the last date entered in "Date Completed" of a sub-task to auto fill that same date into the highest level of the task. 

Comments

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

    Hi Lindsay,

    Will the row change anything after the Date Completed cell is selected?

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    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.

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

    And.

    Am I understanding you correctly that you want to show the last date added and not the latest date?

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am leaning towards LATEST date being the requested info based off of the comparison to the Start and End Date rollups.

     

    If that is the case, you can just use something along the lines of this...

     

    =MAX(CHILDREN())

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    edited 11/05/19

    This formula worked! Is there a way to dictate, if it has children, then this formula, otherwise no formula?

    I have a few lines that I want to work like the hard coded due date column. Pretty much, I am looking for a way to make my own summary value based on the hierarchy. 

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 11/06/19

    Yes, there is.

    Try something like.

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0; MAX(CHILDREN()))

    The same version but with the below changes for your and others convenience.

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, MAX(CHILDREN()))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree's solution is correct for determining whether or not it has children. Just keep in mind...

     

    While this will leave those cells blank that do not have any children, once a date is manually entered that formula will be overwritten and removed from the cell. If you were to adjust the hierarchy after manually entering a date, then you would need to replace the formula in the cell.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭

    Yeah, with many users in a sheet, I can't see this being a good solution. I solution where a user could add their own summary column similar to that of duration and end date with hierarchies would be very useful.  

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Lindsay,

     

    This sounds interesting. Can you explain further? Are you able to build a mock up of sorts to provide screenshots of what it would look like and exactly how it would work?

     

    That way either we can help you find a more fitting solution than those already presented, or you will have a good foundation with which to Submit a Product enhancement Request.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    edited 11/06/19

    In this example, I want the columns with the thick red arrows to roll up the same way the native columns in project sheet do based on hierarchy (Due Date column was renamed from End). 

    I am currently doing this in the Hours column with formulas; the formula gets erased though as the sheet gets manipulated which makes it not an effective way to move forward. 

    It would be advantageous to be able to add hard coded columns like the ones provided. This way the sheet can still be manipulated without fear of losing formulas, data, and keeping data integrity not only within the sheet, but also reports/dashboards.  

    Smartsheet explanation.PNG

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!