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

  • 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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!