JOIN COLLECT - PARENT criterion
Hi there!
I'm doing a cross sheet formula where I want to collect Task Names if the IP Submission Return of the child row (10/12/24 in this example) matches the Most Recent Update of the parent row. The Most Recent Update column utilizes the formula =MAX(DESCENDANTS([Actual IP Submission Date]@row), DESCENDANTS([IP Submission Return]@row)).
Here is a look at the reference sheet {Approval Tracker}.
This is my current formula:
=JOIN(COLLECT({Approval Tracker Name}, PARENT({Approval Tracker Recent Update}@row), {Approval Tracker IP Return}), CHAR(10)))
It returns #UNPARSEABLE
I do want to add another criteria, but I've already got that one working, so I'm not worried about that yet.
I'm pretty sure the issue is with the PARENT formula, but I can't figure it out. Please help!
Best Answer
-
Ah. Ok. The comparison won't be able to happen within the formula like that. You'll need a column on the source sheet that flags rows where the dates match.
=IF([Date Column]@row = [Other Date Column]@row, "Yes", "No")
Then your INDEX/COLLECT would pull from this yes/no column.
=INDEX(COLLECT({Range To Pull}, {Yes / No Column}, @cell = "Yes"), 1)
Answers
-
You'll need a helper column on the first sheet that brings the parent row data onto each of the child rows and then reference the helper column in your COLLECT function. We can't use hierarchy based functions like the PARENT function with cross sheet references.
-
Okay I created a helper column and am still having issues.
=JOIN(COLLECT({Submissions + Approval Tracker Name}, {Submissions + Approval Tracker IP Return}, {Submissions + Approval Tracker Update Help}), CHAR(10))
When I run the formula, it comes up as blank, as though nothing matches that criteria.
-
Do you have a screenshot of the sheet this formula is going on? You have no criteria listed. Only ranges.
-
It's not pulling any information from the sheet the formula is going on. The formula should do this:
Collect and Join Sheet1 Name values where Sheet1 IP Return matches Sheet1 Most Recent Update Helper. -
Ah. Ok. The comparison won't be able to happen within the formula like that. You'll need a column on the source sheet that flags rows where the dates match.
=IF([Date Column]@row = [Other Date Column]@row, "Yes", "No")
Then your INDEX/COLLECT would pull from this yes/no column.
=INDEX(COLLECT({Range To Pull}, {Yes / No Column}, @cell = "Yes"), 1)
-
Gotcha. Thank you!
-
Happy to help. 👍️
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!