Locating the parent of a parent

01/05/21
Accepted

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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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:[email protected], 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([email protected] = 2, INDEX([Task Name]:[Task Name], [Grandparent Row]@row))

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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:[email protected], 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([email protected] = 2, INDEX([Task Name]:[Task Name], [Grandparent Row]@row))

    thinkspi.com

  • Dale MurphyDale Murphy ✭✭✭✭✭

    @Paul Newcome thanks for the ideas. I didn't fully deploy your pattern, but you provided some very useful concepts which I used:

    • counting from top of sheet to identify "grandparent" rows.
      • =IF(COUNT(ANCESTORS()) = 0, COUNT([Task Name]$1:[Task Name]@row), 0)
    • embedding an INDEX statement inside an IF statement
    • the text I need to find and copy is in a column called Task Name (I insert "Unit Summary" if the row is actually the grandparent - has no ancestors.)
    • The column I use to find grandparents is Rowcount
    • the results of the search are inserted in a column called Unit (that is the column that holds that long formulae.

    In the end, the outstanding challenge is that these formulae cannot be converted into column formulae so there is a bit of end-user risk.

    Cheers,

    dm

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Using the method I outlined above, we can make some tweaks so that everything can be converted to column formulas. The only one in my solution that cannot is the Row # column. To be able to convert this into column formulas, we need to add one more column. An auto-number column. The formatting of the auto-number does not matter. Then the formula to produce the row number would be...

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)


    Then everything can be converted into column formulas.


    We can even re-work my final formula to include the "Unit Summary" like so:

    =IF([email protected] = 0, "Unit Summary", INDEX([Task Name]:[Task Name], [Grandparent Row]@row))

    thinkspi.com

  • There should be a =TOPPARENT() function. These answers are so obtuse!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Josh Goldberg Feel free to Submit a Product Enhancement Request.


    Although... I feel like if we are going to go that route then we could maybe tweak it a little bit. Instead of a TOPPARENT function to pull from level zero, why not a function where we can specify which level to pull from? Maybe a tweak to the existing ANCESTORS function where we can specify the level number to add more flexibility to the function instead of a rigid TOPPARENT type of idea?

    thinkspi.com

Sign In or Register to comment.