Pulling next date from a sheet

ACase

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!

Paul Newcome

Would you want it to show the next "0%", or would you want it to continue showing the "Create LucidChart using Salesforce Contacts" line even when it is greater than 0% until it moves on because it has reached 100%?

ACase

https://community.smartsheet.com/discussion/comment/335549#Comment_335549

I think I want it to show the next chronological item that is anything except 100%. Does that make sense?

Paul Newcome

Ok. The challenge there is that your parent row ("Renewal Preparation") is also not yet at 100%. You will need to insert a checkbox column called "Task" with this column formula:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)

Then you can INDEX/COLLECT to pull in the appropriate row data.

=INDEX(COLLECT({Column To Pull From}, {% Complete Column}, @cell <> 1, {Task Checkbox Column}, @cell = 1), 1)

ACase

@Paul Newcome I added the column formula to the project plan sheet, however when I use the INDEX/COLLECT formula in my tracker sheet referencing the project plan, it comes back unparseable. Am I supposed to use the formula and reference the Start Date range?

Paul Newcome

Are you able to provide a screenshot of the formula actually in the sheet?

ACase

@Paul Newcome Here you go:

=INDEX(COLLECT({Renewal Project Plan Range 1}, {Renewal Project Plan Range 4}, @cell <> 1, {Renewal Project Plan Range 3, @cell=1), 1}))

Paul Newcome

You need to remove one of the closing parenthesis from the very end, and the curly bracket at the end needs to move to immediately after "Range 3" and before the comma.