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

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

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

@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
 =IF(COUNT(ANCESTORS([Task Name]@row)) = 0, "Unit Summary", IF(Rowcount@row <> 0, INDEX([Task Name]:[Task Name], Rowcount@row), INDEX([Task Name]:[Task Name], MAX(Rowcount$1:Rowcount@row))))
 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 enduser risk.
Cheers,
dm
 counting from top of sheet to identify "grandparent" rows.

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(Level@row = 0, "Unit Summary", INDEX([Task Name]:[Task Name], [Grandparent Row]@row))

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?

Totally agree... would like to see ANCESTORS tweaked to allow finding the ancestor at N "level up".
Will submit a request.

A few years later, has this been addressed?
@Paul Newcome your initial solution works until I get to "Grandparent Row" when I get "unparseable"  is this still the cleanest solution for this?
Many thanks!


The ([Grandparent Row]) formula below is giving be an #unparseable error. Any idea as to why?
=MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row)), Level:Level, 0)

Looks like some misplaced parenthesis.
=MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row, Level:Level, 0))

Thanks for responding  this worked!
Thanks for your help, I'm brand new to Smartsheet.

Help Article Resources
Categories
Check out the Formula Handbook template!