Is there a formula to determine what the next task is?
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!
Answers

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!

Thanks for the idea. I'll see if I can get it to work.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!