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
-
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)
Answers
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!