Searching through the children rows
I'm trying to write a function that searches through the children rows of parent rows and if one of the rows meet a condition ( "In-Use" ) in the use column, it'll display a name in the User column on the parent row to show who is the current user of a particular asset. I've been trying to use CHILDREN, DESCENDANTS, etc... but I can't come up with a function to make this happen.
Any suggestions would be very helpful!
Thank you,
Hunter G.
Comments
-
Hi Hunter,
Try something like this.
=INDEX(CHILDREN(User@row); MATCH("In-Use"; CHILDREN(Use@row); 0))
The same version but with the below changes for your and others convenience.
=INDEX(CHILDREN(User@row), MATCH("In-Use", CHILDREN(Use@row), 0))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
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.
-
Hey Again,
That worked great! I just want to try and format it and see if I can get it to say Decommissioned instead of #NO MATCH just for ease of use.
Thank you very much,
Hunter G.
-
Excellent!
Happy to help!
To change the message to Decommissioned we only need to add an IFERROR function like below.
Try something like this.
=IFERROR(INDEX(CHILDREN(User@row); MATCH("InUse"; CHILDREN(Use@row); 0)); "Decommissioned")
The same version but with the below changes for your and others convenience.
=IFERROR(INDEX(CHILDREN(User@row), MATCH("InUse", CHILDREN(Use@row), 0)), "Decommissioned")
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
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.
-
Hello,
I am trying to perform a similar function however when I apply the same logic, I'm getting #UNPARSEABLE. I am trying to show the NEXT MOVE DATE on the parent row of a child row when the Trailer Status is "Current Client"
Here is my Formula
=INDEX(CHILDREN(NEXT MOVE DATE@row,MATCH("Current Client",CHILDREN(Trailer Status]@row,0))
-
Got this figured out, looks like i was missing the brackets around the columns.
=INDEX(CHILDREN([NEXT MOVE DATE]@row), MATCH("Current Client", CHILDREN([Trailer Status]@row), 0))
-
Hi Corey,
Excellent!
Glad you figured it out!
Be safe and have a fantastic week!
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives