Locating the parent of a parent

Dale Murphy

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?

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

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

Dale 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
  • =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 end-user risk.

Cheers,

dm

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

Josh Goldberg

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

Paul 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?

Olli Matikainen

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

Will submit a request.

MartinBDigital

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!

Paul Newcome

@MartinBDigital Which solution exactly?

alexhemmert

https://community.smartsheet.com/discussion/comment/273908#Comment_273908

@Paul Newcome

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)

Paul Newcome

Looks like some misplaced parenthesis.

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

MartinBDigital

https://community.smartsheet.com/discussion/comment/367249#Comment_367249

Thanks for responding -- this worked!

Thanks for your help, I'm brand new to Smartsheet.

Paul Newcome

@MartinBDigital Happy to help. 👍️