Pulling next date from a sheet

Hello! I am looking for assistance with a formula that can pull in the next action date from one sheet to another. For context, we use sheets for customer renewal project plans. We also have a tracker that gives an overview of all the project plans and their name, completion, next action date, status, etc. What I would like to do is pull in the next action date from the project plan sheet into the tracker. For instance, in the example below, "Prepare a summary of relevant new features in the last year's software releases" is complete, so I would like to write a formula that pulls in the next action item's date. In this example, since the first item is complete, the formula would pull in 8/25/22 from the "Create LucidChart using Salesforce Contacts" line since that is next up.

I would like to do this with many things in the project plan. Not shown above are the next action person as well and I'd like to pull that the same way as I pull the date.

The closest I've come to this is the Join formula, but I don't think it does quite what I'm wanting it to do. Thanks in advance for the assistance!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!