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

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.


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

  • Perfect - thankyou very much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!