Setting a range using a hierarchy

Hi, I've got an INDEX function that looks up values in a group of rows under a parent in the same sheet.

=INDEX(ID$3:$Goal$183, MATCH(ID214, $ID$3:$ID$183, 0), 5)

Instead of having $183 I'd like to use a function to set the range to everything under the parent (in row 3), no matter how deep it is. Is there a way to do this?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try...

    CHILDREN(ID$3)

    for your range.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ib
    ib
    edited 06/22/20

    Right but that only gets the first level children (e.g. in the case count returns 5), I would need to iterate through all children so i get the full range. Unless I'm missing what Children() returns since

    =INDEX(ID$3:CHILDREN(ID$3), MATCH(ID205, $ID$3:$ID$176, 0), 5) = "Unparseable"

    I initially tried something like , but i would need an "offspring" or something similar to the Ancestor() function to get all children.

    =INDEX(ID$3:"$Goal"+COUNT(CHILDREN(ID$3)), MATCH(ID205, $ID$3:$ID$176, 0), 5)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry about that. I didn't realize you had multiple levels of hierarchy. Try a DESCENDANTS function like so...

    =INDEX(DESCENDANTS(ID$3), MATCH(ID214, DESCENDANTS($ID$3), 0), 5)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ib
    ib

    So i've tried that, and i still get "INVALID VALUE", does Descendants return both a row and column range or just the row range?

  • ib
    ib

    I tried doing a concatenation but no joy: INDEX(ID$3:"Goal"+(COUNT(DESCENDANTS(ID$3),...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My apologies. I misread your original formula.

    =INDEX(DESCENDANTS($Goal$3), MATCH(ID214, DESCENDANTS($ID$3), 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!