Current Milestone task name for the current row

Andy Counsell

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

Genevieve P.

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

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.

Smartsheet .pdf

Genevieve P.

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

Vicky

Thank you Genevieve I will give it a go.