Secondary Milestones connected to the Main Milestones

Stash

I’m looking for assistance with a project I’m managing in Smartsheet. Specifically, I want to display two values next to each of my MAIN MILESTONES:

1. The number of SECONDARY MILESTONES that are connected to each MAIN MILESTONE.
2. The sum of quotes corresponding to those SECONDARY MILESTONES (from the column QUOTES)

It’s important to note that the SECONDARY MILESTONES are not technically children of the MAIN MILESTONES in Smartsheet’s structure. Instead, they are connected via the arrows shown in the Gantt chart view, which indicates their presence in the Predecessors column.

I would know how to achieve this if I could use the values in the Predecessors column as text. Unfortunately, it seems that Smartsheet does not allow conversion of the Predecessors column values to text, nor can I directly reference the contents of the Predecessor rows.

My question is: Is there any way to display the count of connected SECONDARY MILESTONES and their corresponding quotes for each MAIN MILESTONE in Smartsheet? Any suggestions or workarounds would be greatly appreciated!

Thanks in advance for your help!

jmyzk_cloudsmart_jp

Hi @Stash

Here is my demo solution. For details, please check the formulas.

https://app.smartsheet.com/b/publish?EQBCT=669bd82a9f674965a9916dad93d8aa30 (Link to the published demo sheet: read-only)

Formula Breakdown

Identifying Milestones:

[is Milestone] = IF(Duration@row = 0, 1)

• This formula checks if a task is a milestone based on whether its duration is zero.

Identifying the Main Milestone:

[Is Main Milestone] = IF(AND([is Milestone]@row, Successors@row = MAX([Row No]:[Row No])), 1)

• Here, a task is marked as the main milestone if it is a milestone and if its row number equals the maximum row number in the Successors column, indicating it’s the last dependent task.

Finding the Last Main Milestone Row Number:

[Last Main Milstone Row No.] = MAX(COLLECT([Row No]:[Row No], [Is Main Milestone]:[Is Main Milestone], 1, [Row No]:[Row No], <[Row No]@row))

• This calculates the row number of the last main milestone before the current row, helping to segment the project phases.

Finding the Next Main Milestone Row Number:

[Successor Main Milstone Row No] = MIN(COLLECT([Row No]:[Row No], [Is Main Milestone]:[Is Main Milestone], 1, [Row No]:[Row No], >[Last Main Milstone Row No.]@row))

• This determines the row number of the next main milestone after the current main milestone, useful for bounding the current project phase.

Identifying Secondary Milestones:

[is Secondart Milestone] = IF(AND([is Milestone]@row, CONTAINS([Successor Main Milstone Row No]@row, Successors@row)), 1)

• This marks a milestone as secondary if it falls within the scope of the successors of a main milestone determined by the main milestone's row number.

Joining Successors:

[Successors] = JOIN(SUCCESSORS([Task Name]@row), ",")

• Concatenates the successors into a comma-separated string for easier viewing and reference.

Counting Secondary Milestones:

[Secondary Milestones Count] = IF([Is Main Milestone]@row, COUNTIFS([is Secondart Milestone]:[is Secondart Milestone], 1, [Row No]:[Row No], AND(@cell > [Last Main Milstone Row No.]@row, @cell < [Successor Main Milstone Row No]@row)))

• Counts secondary milestones between the last and the next main milestone when the current row is a main milestone.

Summing Quotes for Secondary Milestones:

[Sum of Quotes] = IF([Is Main Milestone]@row, SUMIFS(Quotes:Quotes, [is Secondart Milestone]:[is Secondart Milestone], 1, [Row No]:[Row No], AND(@cell > [Last Main Milstone Row No.]@row, @cell < [Successor Main Milstone Row No]@row)))

• Sums the quotes of secondary milestones that fall between the last and next main milestone for each main milestone.