Formula to capture Current Milestone and Current Task

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?


Best Answer

Answers

  • Zachary Hall
    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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!