Reference EACH child from the Parent on Another sheet

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).


Sherry Fox

Data Science & Reporting Specialist | Information Technology

United HealthCare Services (UHS)

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Answers

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    Now that makes perfect sense @Genevieve P. ! Thanks so much for taking the extra time to explain the formula based on my screenshot. That really helps so much. I just could not get it before! LOL

    Sherry Fox

    Data Science & Reporting Specialist | Information Technology

    United HealthCare Services (UHS)

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!