Current Milestone task name for the current row

Hi

Relatively new to smartsheet and getting to grips with sum of the hierarchy functions


I want a formula that returns the task name for the milestone of any given task.

or the task name of the last child


any help very much apreciated

Tags:

Answers

  • Hi Andy,

    There may be a better way of achieving this but I have one idea you can try. Are you using Dependencies on your sheet? Do you have Project Settings enabled? If you do, and your main Parent Task automatically has the last end date populated, then this formula may work for you:

    =INDEX(CHILDREN([Task Name]:[Task Name]), MATCH([End Date]$1, CHILDREN([End Date]:[End Date])))


    This looks through the Task Name column and returns the task that matches the End date of the Parent row with the End Date of the last task. I've locked in the top parent end date with the $ sign: [End Date]$1

    You will need to update this number to each parent row's number. Does that make sense?

    Let me know if this will work for you! If not, it would be helpful to see a screen capture of your sheet (but please block out any sensitive data).

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Vicky
    Vicky âś­

    Hi Genevive,

    I'm having a similar problem but might be me not understanding how it works also being new to Smartsheet and trialling the product to try and create a business case. I've attached screenshots which hopefully explain my issue. I'm using a template set and can't see how the milestones from the Plan are linked to the Milestones report so I can update it to include the new milestones I've added.


  • Hi @Vicky

    Based on your screen captures, are you wanting the "Produce Statement of Works" task to show up as a milestone? If so, I can see that this task is longer than just one day, so you will need to adjust the duration.

    For milestones to show up in the Gantt chart as a diamond, the start and end date need to be the same, with the task duration of 0.

    You can create an extended milestone by entering a task duration of ~0 and a start date. An extended milestone requires one full working day to complete, but is still represented by a diamond shape in the Gantt chart.

    (Read more about milestones in our Help Center, here!)

    If the task still isn't showing up in your Report, it would be helpful to see what the criteria of the report is. To find this, click on the small blue folder icon in the top-left of the report (see more about Reports here).

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Vicky
    Vicky âś­

    Thank you Genevieve I will give it a go.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!