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.

Asset Allocation Issue.png

Find more posts tagged with

IT & Operations

Comments

Andrée Starå

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

Hunter Gade

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.

Andrée Starå

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?

Corey Bailey

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))

Corey Bailey

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))

Andrée Starå

@Corey Bailey

Hi Corey,

Excellent!

Glad you figured it out!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Jarret.Birdwell

https://community.smartsheet.com/discussion/comment/191736#Comment_191736

Hello Andrée,

I am trying to do this search, but it give "#INVALID DATA TYPE" when the index/match is in a column formula. Is there a way to change the index/match formula to allow it to search the column formula?

Context: I have a formula that gives a status of a task based on % completion & end dates within 30 & 15 days of Today(). I want the parents to search their children for "Late" or "At Risk" status and have that become the status so my team can more easily keep track of up coming deadlines.

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.