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!
Answers
-
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%?
-
I think I want it to show the next chronological item that is anything except 100%. Does that make sense?
-
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)
-
@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?
-
Are you able to provide a screenshot of the formula actually in the sheet?
-
@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}))
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!