return the content of a column and its corresponding start date
Answers
-
Hi @Genevieve P. ,
I am hoping you can help me with a similar issue. I have a sheet of data which has parent and child rows. There are multiple activities either completed, in progress or not started.
What I would like to do, either in the Summary Sheet or the main sheet, is to return the content of a column and its corresponding start date, given that there are other sections above the data with activities in various stages of completeness.
For example, in this snap-shot, I would like to create a field that would display "Activity 2" and another field that would display "07/09/22" based on the fact that this is in phase 1 of the Monitoring section and is the next activity that is due to be started (i.e. "Activity 1" is set to complete).
How would I do this?
Thanks in advance for any support you can provide!
-
Hey @RKemp
Try this. Please verify my column names and edit the formula to replace the names with your column names. I took a guess at [Task Name], Status and [Start Date]
The first IF in the formula is checking if the row is a sub-parent, and has a status of 'In Progress'. Depending on how your 'In Progress' is determined you might not need the second IF, which looks to make sure there are still come Children rows incomplete. Assuming the first two IFs statements are true, the INDEX Collect should pull the next incomplete child row.
Your 'content' helper column
=IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, COUNT(CHILDREN([Task Name]@row)) > 0, Status@row = "In Progress"), IF(COUNTIFS(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started")) > 0, INDEX(COLLECT(CHILDREN([Task Name]@row), CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started"), CHILDREN([Start Date]@row), AND(ISDATE(@cell), @cell = MIN(@cell))), 1)))
Your 'next date' helper column (don't forget to make this a Date column)
=IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, COUNT(CHILDREN([Task Name]@row)) > 0, Status@row = "In Progress"), IF(COUNTIFS(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started")) > 0, INDEX(COLLECT(CHILDREN([Start Date]@row), CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started"), CHILDREN([Start Date]@row), AND(ISDATE(@cell), @cell = MIN(@cell))), 1)))
Would this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!