Formula for First Level Parent

Natalie Gorman

Hi All

I am trying to reference a top level Parent row for a child row. The parent formula only returns the immediate parent to that child. Is there a way I can determine which level of parent to return using a formula?

Many thanks

Natalie Gorman

Bassam Khalil

Hi @Natalie Gorman

Hope you are Fine, you can create helper column for chilled level then you can determine which level of parent to return using this helper column , check the following vedio to learn how you can create the level helper column:

https://www.youtube.com/watch?v=f-2p6Zz-6FA

Natalie Gorman

Thank you @Bassam.M Khalil

The video was very helpful. Using this helper column my top level parent is always going to be number 1. What formula would I apply to the column that I want to populate with the text of the parent row (ie so it always returns the text of parent level 1)?

Many thanks for your help.

Kind regards,

Natalie

Dale Murphy

@Natalie Gorman I think I just solved for something similar.

I use one extra column (called Rowcount) to locate [grand]parent rows:

=IF(COUNT(ANCESTORS()) = 0, COUNT([Task Name]$1:[Task Name]@row), 0)

where Task Name is my primary column.

Then in a 3rd column called Unit I check against that result to get the right text from Task Name and insert it into Unit (if I am at the highest level Ancestors=0 which is a row used for a summary):

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

dm

Natalie Gorman

Thank you so much @DMurphy ! That works perfectly. I haven't quite got my head around all of the formula in terms of understanding exactly what each part is doing, but I can figure that out in slow time.

Many, many thanks.

Natalie