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
-
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!
-
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)
-
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!
-
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?
-
I tried doing a concatenation but no joy: INDEX(ID$3:"Goal"+(COUNT(DESCENDANTS(ID$3),...
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!