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?

Find more posts tagged with

Accepted answers

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)

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.