Searching through the children rows

Hunter Gade · 07/25/19

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

Andrée Starå · 07/26/19

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 · 07/30/19

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å · 08/02/19

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 · 04/14/20

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 · 04/15/20

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å · 04/16/20

@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 · 11/25/24

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.

Searching through the children rows

Comments

Categories