How do I always reference a child row immediately below the parent row(In the same column)?

Steve Richardson

Probably a basic way to do this but I cant get it to work.

I need the circled cell to always refer to the cell immediately below it. So the circled cell will show "OFF HIRE' from the blue row until a new row is inserted in between the 2 and then it will refer to the new row's cell.

Kelly Moore

Hey @Steve Richardson

This approach will require the addition of a helper column, if it doesn't already exist in your sheet. We use the system generated auto-number column. It will default to a column name of [Row ID]. Insert a column and choose system generated auto-number column as the column property type.

Once created (and saved) enter this formula in the appropriate cell

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

This says to give the Child of that column where the Row ID of that Child matches the minimum Row ID that is greater than the row you are sitting on. As you slide rows around, the row ID will change as you slide, which should make any row you slide to the blue position pop up as the answer. If you didn't slide the rows around and always entered any new children at the bottom of the child section we would change the Min to a Max- assuming all other rows kept their chronological positions.

Will this work for you?

Kelly

Kelly Moore

Hey @Steve Richardson

This approach will require the addition of a helper column, if it doesn't already exist in your sheet. We use the system generated auto-number column. It will default to a column name of [Row ID]. Insert a column and choose system generated auto-number column as the column property type.

Once created (and saved) enter this formula in the appropriate cell

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

This says to give the Child of that column where the Row ID of that Child matches the minimum Row ID that is greater than the row you are sitting on. As you slide rows around, the row ID will change as you slide, which should make any row you slide to the blue position pop up as the answer. If you didn't slide the rows around and always entered any new children at the bottom of the child section we would change the Min to a Max- assuming all other rows kept their chronological positions.

Will this work for you?

Kelly

Steve Richardson

Perfect - thankyou very much!

InvitedChaos

I know this is reply is now years after the fact, but there actually is a much, MUCH simpler way:

=INDEX(CHILDREN(), 1)