Search Index/Match in All Children of a Shared Parent

Jarret.Birdwell

Hello,

I am attempting to automate adding terms to a project while keeping our original EVMS data using a column formula. I am having difficulties with adding logic to search for a specific phrase within the same parent.

I am trying to get a formula that will index the row "Item 1 Ordered" by replacing "Arrival" with "Ordered" in the item name. When I manually select all the children of parent("Item 1") the formula works as written:

When I try to format the formula to work as a column formula for my entire sheet by attempting to search the range Children I either have to input the parent row number or it doesn't work. Obviously I can't only search only a single child for a much larger sheet with many ancestors. Children attempt below:

I get no match with only child of the target row, it has no children to search which makes sense and shows that the Children function doesn't break the logic.

I get #UNPARSEABLE when I do children of the parent at row:

My reading of the Smartsheet support topics and testing is that =Parent(@row) gives me the information at row "Item 1", =Children("Item 1") gives me the range of all children, but =Children(Parent()) is #UNPARSEABLE.

Why does =Children(Parent(@row)) not give me a range of the children of the parent of the current row?

=Children(Parent(@row)) -> =Children("Item 1") -> range of children for "item 1" ?

Paul Newcome

We cannot currently nest hierarchy based functions. You will need to put a helper column in and use a formula to output the parent row data on every row. You can then move to an INDEX/COLLECT to be able to index a range of data based on multiple range/criteria sets.

Paul Newcome

We cannot currently nest hierarchy based functions. You will need to put a helper column in and use a formula to output the parent row data on every row. You can then move to an INDEX/COLLECT to be able to index a range of data based on multiple range/criteria sets.

Jarret.Birdwell

Thank you, Paul,

I found an alternative solution as well. I have built the sheet with all base level children tasks as =parent()+" Text" to reduce repeat work since all tasks share the same base make up. I am able to have the full range with my Left & Len function that will capture the range I am looking for.

Not sure if it will bog down my sheet by having to search all +5000 rows, but it works in my test environment.