Return Next Pending Task in Summary Sheet

Stacy Divin

Apologies if this has been asked before, but I'm struggling to find what I need and am not the best at formulas. I want to include a field in a sheet summary that shows the next pending task, but can’t seem to get it to work. Here’s a bit of an explanation…

Let’s say I have a sheet with two columns…Status and Task.

Basically, I want a formula that returns the Task if the Status equals “Not Started” or “In Progress”. INDEX/MATCH works perfect if I’m looking for one criteria; such as:

=INDEX([Task]:[Task], MATCH("In Progress", Status:Status, 0))

But, I’m struggling to put something together that returns the Task if I’m looking for one of two different criteria. I’ve played around with several different functions and can’t seem to get it right. More than likely something simple I’m overlooking…

Would you guys be able to point me in the right direction on this? 

Paul Newcome

Ok. Try this...

=INDEX(COLLECT([Document/Deliverable/Task]:[Document/Deliverable/Task], Status:Status, @cell <> "Complete"), 1)

Stacy Divin

Paul, you rock!

I failed to mention that the Status column has a few other options, so I couldn't go with a "does not equal Complete" formula, but what you provided sent me in the direction I needed to go. I replaced <> "Complete" with an OR function and it does exactly what I wanted it to. Here it is for reference:

=INDEX(COLLECT([Document/Deliverable/Task]:[Document/Deliverable/Task], Status:Status, OR(@cell = "In Progress", @cell = "Not Started")), 1)

Thank you so much!

Paul Newcome

You are most likely going to need to use something along the lines of an INDEX/COLLECT. If you can describe your process in more detail and/or provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data", that would really be helpful in providing you with a more detailed solution.

Stacy Divin

Thanks much, Paul.

I produce a report each week for our team that provides a status of outstanding projects, including what the next pending task is. Initially, using an INDEX/MATCH that looked for the first matching row with a status of "In Progress" worked fine, but we do run across situations where the next pending task in a sheet is set to "Not Started" and I want to make sure we also capture those. Hence wanting to develop this.

The way our sheets are currently structured is a basic one task per line with a status column assignees can update. I've included a screenshot with dummy data below. Using this data, the formula should return Task C as the result. If the assignee were to mark Task C as Complete, the formula should then return Task D (rather than Task E which is what the INDEX/MATCH formula I was previously using would do).

Let me know if this isn't helpful or if you need any additional info.

Paul Newcome

What is the INDEX/MATCH you are currently using?

Stacy Divin

Hey Paul,

Here's what I'm currently using to return the first row matching "In Progress"

=INDEX([Document/Deliverable/Task]:[Document/Deliverable/Task], MATCH("In Progress", Status:Status, 0))

Paul Newcome

Ok. Try this...

=INDEX(COLLECT([Document/Deliverable/Task]:[Document/Deliverable/Task], Status:Status, @cell <> "Complete"), 1)

Stacy Divin

Paul, you rock!

I failed to mention that the Status column has a few other options, so I couldn't go with a "does not equal Complete" formula, but what you provided sent me in the direction I needed to go. I replaced <> "Complete" with an OR function and it does exactly what I wanted it to. Here it is for reference:

=INDEX(COLLECT([Document/Deliverable/Task]:[Document/Deliverable/Task], Status:Status, OR(@cell = "In Progress", @cell = "Not Started")), 1)

Thank you so much!

Paul Newcome

Great! Happy to help! 👍️