Join Sub-Parent with Children rows

Beth Klineman

I have a sheet that has multiple parent rows and I want to join a specific Parent Row to the children rows. When I use the formula =JOIN(ANCESTORS([High-Level Workflow Task]@row), "-") it includes all the parent rows above it like this "SM RDG LO-SCO Migration-FY2023 Budget & Scope-Gadget Group X: Thin Slice#1 @5%-Batch 1/1 (150 SCO's)" I only want the Join to show "Gadget Group X: Thin Slice#1 @5%" (and remove the Batch 1/1 if possible but that is not as important as removing the higher parents

Here is a screenshot of what it is including

Paul Newcome

You would "add" the two together with a "space hyphen space" in the middle.

=INDEX(ANCESTORS([Primary Column Name]@row), 3) + " - " + INDEX(ANCESTORS([Primary Column Name]@row), 4)

Paul Newcome

Try this:

=INDEX(ANCESTORS([Primary Column Name]@row), 3)

That 3 is what determines which level to pull from with 1 being the leftmost indentation level (SM RDG LO-SCO Migration / dark blue with white text).

Beth Klineman

@Paul Newcome is there a way to combine two rows in the Join function so I can show something like this for the green cell " Batch 6 - Performance Tasks - Creative Asset Development. It is using the formula you provided but added another hierarchy row. I changed the formula to be =PARENT({Task Name}@row) but this does not work as we get lower in the hierarchy (if that makes sense) The tricky part is I am using this formula in 5 different sheets and the hierarchy is different in each. But ideal I am showing the Task Name in a report but we need to include the hierarchy rows in a detail column (but do not need ALL the hierarchy)

Paul Newcome

You would "add" the two together with a "space hyphen space" in the middle.

=INDEX(ANCESTORS([Primary Column Name]@row), 3) + " - " + INDEX(ANCESTORS([Primary Column Name]@row), 4)

Beth Klineman

Awesome -- I knew it was something like this but couldn't get the formula to work

Paul Newcome

Happy to help. 👍️