Formula for only parent rows

bjohnson0514

I created a helper column to sum all the children rows under a parent row. I want to now create a column formula that only applies to the parent rows, that populates whatever is in the child row below the parent row into the parent row. I have it working if I apply the formula to each parent row but can't get it to work as a column function.

Here is the formula that I am using (copying and pasting into each parent row)

=INDEX(CHILDREN(), MATCH(MIN(COLLECT(CHILDREN([Row ID]@row), CHILDREN([Row ID]@row), >[Row ID]@row)), 0))

Julio S.

Hi @bjohnson0514,

To accomplish what you intend you would need the formula to discriminate between parent and children rows. A possible suggestion would be to add the following statement to your formula: =IF(COUNT(CHILDREN([Primary Column]@row)) >= 1, Value if True (your formula), "")

Note that the "value if false" argument in here is a blank value but you can use this space if you wish to perform a different calculation in children rows. Although I haven't tested this in my own environment the whole collated formula along with yours could look like the following:

=IF(COUNT(CHILDREN([Primary Column]@row)) >= 1, INDEX(CHILDREN(), MATCH(MIN(COLLECT(CHILDREN([Row ID]@row), CHILDREN([Row ID]@row), >[Row ID]@row)), 0)), "")

Feel free to make adjustments as needed if this returns an error or doesn't fully fit your sheet structure.

I hope that this can be of help.

Cheers!

Julio