Help with Index Match bringing back Children row data based on Parent Row Match
How do I get Index/Match to do the following:
Look at Source sheet column for a Match in a Parent row and if there is a match, pull in two date columns for all the CHILDREN.
This formula is pulling in dates but only for the first PARENT/CHILD section. It's not checking the MATCH on the Parent Asset #.
=INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Deliverables and Tasks]@row), {Test AMT Asset/Tasks}, [Deliverables and Tasks]@row), 1)
This formula say's Unparseable.
=(INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Deliverables and Tasks]@row), {Test AMT Asset/Tasks}, [Deliverables and Tasks]@row), MATCH(PARENT({Test AMT Asset #}, {Asset #}, 1))))
Thanks!!
Best Answer
-
I suggest a helper column where you replicate the parent row data on each of the child rows. Then you should be able to use an INDEX/COLLECT and include this helper row as a range/criteria set.
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!
Answers
-
Hi Jeana,
Unfortunately, it doesn't work with parents.
The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS.
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Bummer! Thanks for the confirmation. Now to ponder a work around. :-)
Jeana
-
So if I eliminate the PARENT/CHILD relationship my sheet looks like this:
My goal is to pull in the ACTUAL START DATE and ACTUAL END DATE into this sheet based on the Asset # and Deliverables and Tasks (this is what is unique).
I've tried several variations of INDEX COLLECT but can't seem to get it right.
=INDEX(COLLECT({Test AMT Task Started}, MATCH(COLLECT([Asset #]@row, [Deliverables and Tasks]@row), {Asset #}, {Test AMT Asset/Tasks}, 0)))
-
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Your solution is for Excel and not Smartsheet so that it won't work, unfortunately.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå thanks for the offer. I don't have this exact issue anymore but a similar one. I have two sheets that I can use Index/Match on to return data on that row (where the match occurs). I'd like to be able to pull data from the CHILDREN of the row that Index/Match returns. Here's a screen shot that should help.
If INDEX/MATCH is successful for Title for this Template, can I return data in Step 1 or other Steps?
Thanks for your time!!!
Jeana
-
I suggest a helper column where you replicate the parent row data on each of the child rows. Then you should be able to use an INDEX/COLLECT and include this helper row as a range/criteria set.
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!
-
Excellent!
You're more than welcome!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
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!