Formula for First Level Parent

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

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/09/20

    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:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Natalie Gorman
    Natalie Gorman ✭✭✭✭
    edited 12/17/20

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!