Vlookup of Child tasks
Hello,
I have a sheet with Unique parent tasks. The Child task however or not unique and repeat within each different Parent Task.
Example
(Parent Task) Job 10
Child Task 1 Paid
Child Task 2 not Paid
(Parent Task) 11
Child Task 1 Paid
Child Task 2 not Paid
What I need to do is retrieve data from a column in another sheet that has the same parent and child tasks from the original Sheet.
Answers
-
You would need to have a column on the lookup sheet that also contains the parent row data on each of the child rows. Then you can use an INDEX/COLLECT to be able to reference both columns.
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!
-
Hi Paul,
Thank you, yes the Column on the lookup sheet contains the same data.
I’ll give this a shot to see how close I can get and reply here either way,
John
-
Hi Paul I am still unclear on this not have any luck
-
Here are the two sheets. In the Main sheet I want ot pull over some of the column data related to the permits like the status and the start and complete dates. I would like the information to automatically update in the Main sheet when it is update in the Source sheet.
Thanks for your help
-
Hi @MCSCOO
As long as the value you're searching for is unique (so, "City_PS" doesn't show up anywhere else), you should be able to write an INDEX(MATCH statement as Paul suggested.
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))
So in your case, something like:
=INDEX({Status Column}, MATCH([Task Name]@row, {Task Name Column}, 0))
Let us know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve,
Thank you I am close. However, The "City_PS" does repeat. However it is a child task. The Parent Tasks Are unique and do not repeat. Being that the Parent Task are unique can I isolate the Parent task and then grab the Status from the Child task "City_PS"?
I have had success with the first part of your formula. I think I am just missing this last piece thank
you!
John
-
You need a helper column on the source sheet that contains the parent row data.
=PARENT([Task Name]@row)
Then you would use an INDEX/COLLECT similar to this...
=INDEX(COLLECT({Status Column}, {Helper Column}, @cell = "Submitted", {Task Column}, @cell = "City_PS"), 1)
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!