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.

Ideas?

Best Answer

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

    I would suggest three helper columns.


    One to output the hierarchy level (Level):

    =COUNT(ANCESTORS())


    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))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!