JOIN COLLECT - PARENT criterion

leslie.g

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!

Paul Newcome

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)

Paul Newcome

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.

leslie.g

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.

Paul Newcome

Do you have a screenshot of the sheet this formula is going on? You have no criteria listed. Only ranges.

leslie.g

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.

Paul Newcome

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)

leslie.g

Gotcha. Thank you!

Paul Newcome

Happy to help. 👍️