Retrieve value from "Top Parent" when Ancestor level = 2

Leeweber

I have a project management sheet.

CSYYXXXX is the header for the project name column
Tasks is the tasks column - at the parent row, I just write "Tasks" and then there are child rows below that. Some of these child rows and another level of descendant rows.

I want a column formula (to serve as a helper column for various other functionality) that will give me the CSYYXXXX of the very top parent row.

=parent(CSYYXXXX@row) doesnt work because the level 2 ancestors' CSYYXXXX column is blank

this formula is close

=IF(Tasks@row = "tasks", CSYYXXXX@row, IF(COUNT(ANCESTORS(Tasks@row)) = 1, PARENT(CSYYXXXX@row), IF(COUNT(ANCESTORS(Tasks@row)) = 2, INDEX(COLLECT(CSYYXXXX:CSYYXXXX, Tasks:Tasks, Tasks@row = "tasks"), 1))))

The piece of the formula ,1 is pulling the value from the first row only.

Looking to retrieve the parent(parent(CSYYXXXX@row) but I know you cannot do the "nested parents"

Paul Newcome

Try this:

=IFERROR(INDEX(ANCESTORS(CSYYXXXX@row), 1), CSYYXXXX@row)

Leeweber

They say less is more - definitely the case here - that worked, thank you!

Paul Newcome

Are you able to provide some screenshots for context?

Leeweber

Hi Paul, sorry - should have included those in the first place.

Ancestor column formula: =COUNT(ANCESTORS(Tasks@row))
Formula column formula: =IF(Tasks@row = "tasks", CSYYXXXX@row, IF(COUNT(ANCESTORS(Tasks@row)) = 1, PARENT(CSYYXXXX@row), IF(COUNT(ANCESTORS(Tasks@row)) = 2, INDEX(COLLECT(CSYYXXXX:CSYYXXXX, Tasks:Tasks, Tasks@row = "tasks"), 1))))

The highlighted cells are the "first instance" that the index/collect is pulling. But I need them to be parent(parent(CSYYXXXX@row) even though that syntax doesn't work :)

Paul Newcome

Try this:

=IFERROR(INDEX(ANCESTORS(CSYYXXXX@row), 1), CSYYXXXX@row)

Leeweber

They say less is more - definitely the case here - that worked, thank you!

Paul Newcome

Happy to help. 👍️