Is there a formula to determine what the next task is?

Sara Ross

I would like the Smartsheet Summary to specify what Task needs to be completed next. To make the determination, I think I need to search the the Status column for those items "Not Started" or "In Progress" and then the Due Date column of those items for the one with the earliest due date. The Task (Task column) that meets this criteria would then be displayed. Is there a formula that will serve this function?

*Note, there may be tasks that have the same Due Date, but I just want to identify the next Task on the list including the the parent row.

Any help is greatly appreciated!

Hayley B

Hi Sara!

There may be a much better/faster way to do this, but the only thing I can think is to use a helper column and both IF and VLOOKUPs. This will work if you only ever have one row in the sheet with status of "in progress" at a time. You could have a helper column that just has the number of each row (this may not work if you are constantly adding or moving rows around). And then calculate IF the VLOOKUP (looking for that "In Progress" value) is found, spit out the row number, then add 1. You could then use another VLOOKUP to find that new row #'s task name to return.

Again, this may be too convoluted, but worth a shot!

Sara Ross

Thanks for the idea. I'll see if I can get it to work.