Reference EACH child from the Parent on Another sheet

Sherry Fox

I want to display (from another sheet) the child rows. How can I use a formula, on each of my child lines that displays child1, child2, child3, child4, and child5 in my Release Name & Release column. I only require the ability to do this in these 2 columns. The black text is raw data (and the blue text are formulas).

Genevieve P.

Hey @Sherry Fox

Can I assume that the image above is your Source sheet?

If so, you could use an INDEX(COLLECT formula, referencing the "Release" column as the unique identifier, to bring back row data. It would look something like this:

=INDEX(COLLECT({Release Name Column}, {Release Column}, 202250), 1)

That would bring back the first match, or in this case your parent row. To get the first Child row, you'd swap out the 1 at the end to be 2:

=INDEX(COLLECT({Release Name Column}, {Release Column}, 202250), 2)

And so on:

=INDEX(COLLECT({Release Name Column}, {Release Column}, 202250), 3)

What you could do is have your destination sheet set up so that the Release number and the Child numbers are in two columns, then you can reference these instead of typing them into the formula:

=INDEX(COLLECT({Release Name Column}, {Release Column}, [Release Number]@row), [Child Number]@row)

Let me know if that makes sense!

Cheers,

Genevieve