Locating the parent of a parent


I am trying to take the value from the primary column of a level 0 parent and insert it into another column in a level 2 child.

=PARENT([Task Name]@row) works fine when there is only level 0 and level 1.

Something like parent of a parent is what I have imagined would work, but =parent(PARENT([Task Name]@row)) is unparseable.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest three helper columns.

    One to output the hierarchy level (Level):


    Another to output the Row Number ([Row#]):

    =COUNTIFS(Level$1:Level@row, OR(@cell = "", @cell <> ""))

    And finally one more to pull the row number of the parent of the parent ([Grandparent Row]):

    =MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row)), Level:Level, 0)

    Then we can use this formula to pull from the [Task Name] column based on the number in the [Grandparent Row] column IF the hierarchy of the current row is 2:

    =IF(Level@row = 2, INDEX([Task Name]:[Task Name], [Grandparent Row]@row))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!