How To Reference Top Parent For Each Row

ShaunC

I'm trying to implement a formula that will reference the name of the rows Top/First Parent for each row. I have reviewed other threads on this and have not seen a definitive solution (or just one I could get to work). Ideally looking for something that could then be used as a column formula.

Our sheet is organized into 7 stages and ideally each row under each of these stages would reference the Top/First Parent task name independent of how many levels of hierarchy there are within the stage.

Screen shot of desired future state.

Any help is appreciated!

Paul Newcome

You would use something like this:

=IFERROR(INDEX(ANCESTORS([Task Name]@row), 1), [Task Name]@row)

Paul Newcome

You would use something like this:

=IFERROR(INDEX(ANCESTORS([Task Name]@row), 1), [Task Name]@row)

ShaunC

Yeah that worked great! Appreciate it!

Paul Newcome

Happy to help. 👍️

Tiffany Ross

Thank you! This was a huge help.

Christoph Studinka

Great solution if you need to reference to the parent only…. but I also need to reference to the grand parent and the great grand parent. In other words, I need a text in a cell that combines all the ancestors plus the row itself (with +).

How could I accomplish this?

Many thanks for any help.

Christoph

Paul Newcome

@Christoph Studinka You would use a JOIN function to join the ANCESTORS together.

Christoph Studinka

Thanks Paul, interesting approach. I'll try (I'm curious).

I found a workaround in the mean time, works like a charm, but at the moment I am limited to 4 hierarchical levels… (working with IF/AND plus INDEX/Ancestors and 3 helper columns). Cheers, Christoph

Paul Newcome

@Christoph Studinka It would be very similar to the INDEX function.

=JOIN(ANCESTORS([Column Name]@row), "+") + [Column Name]@row

Christoph Studinka

Wow Paul, this is a very elegant solution! Saves hours of workaround work… Many thanks for this.
Christoph

Christoph Studinka

What if I would want a / between the ancestors:

Here: Three hierarchical levels to be joined together:
General / Functions / Marketing

Thanks again, Christoph

Isis Taylor

@Paul Newcome Thanks for explaining, I have been trying to figure out how to do that as well.

Paul Newcome

@Christoph Studinka You would change the "delimiter" portion of the JOIN function.

@Isis Taylor Glad this thread was able to help. 👍️

Christoph Studinka

Many thanks, works well. The Smartsheet community is of invaluable help. I appreciate this enormously.
Christoph