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 ✭✭✭✭✭
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
1
Answers
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
@Paul Newcome thanks for the ideas. I didn't fully deploy your pattern, but you provided some very useful concepts which I used:
In the end, the outstanding challenge is that these formulae cannot be converted into column formulae so there is a bit of enduser risk.
Cheers,
dm
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 autonumber column. The formatting of the autonumber does not matter. Then the formula to produce the row number would be...
=MATCH([AutoNumber]@row, [AutoNumber]:[AutoNumber], 0)
Then everything can be converted into column formulas.
We can even rework 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!
@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