Formula to capture Current Milestone and Current Task

Vivien Chong

I would like to surface the Current Milestone and Current Task for reporting purposes.

In the following example, current milestone is the task with milestone column checked and Status is In Progress.

Current task is the task with milestone column unchecked and Status is In Progress. However, it should not show "EXECUTION" as current task because EXECUTION will be in progress till the entire children is completed.

Do you have any idea how should I go about formulating a Search Formula to surface the relevant task/milestone?

Paul Newcome

Current Milestone:

=INDEX(COLLECT([Task Name]:[Task Name], Milestone:Milestone, @cell = 1, Status:Status, @cell <> "Complete"), 1)

Current Task:

=INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete"), 3)

Zachary Hall

Since you can't use arrays in Smartsheet you wouldn't be able to pull the second match using a match formula, so I would probably use a hidden helper column to determine if the row was a Child row in progress to return the result. There may be a more graceful way to do it, but I personally like the simplicity of this solution.

Paul Newcome

Current Milestone:

=INDEX(COLLECT([Task Name]:[Task Name], Milestone:Milestone, @cell = 1, Status:Status, @cell <> "Complete"), 1)

Current Task:

=INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete"), 3)