Retrieve Level 1 Text Values

Row #    Level    Task Name                                                     Retrieve Phase Name

1               0           + Training Project                                          N/A

2               1                 + Phase 1: Pre Train                                Phase 1: Pre Train

3               2                        + Email Communications               Phase 1: Pre Train

4               3                                 Review Initial FE Comms       Phase 1: Pre Train

5               3                                 Launch New Email                  Phase 1: Pre Train

6               2                        + Kick-off Call                                    Phase 1: Pre Train

7               3                                 Kick-off call outline                  Phase 1: Pre Train

8               3                                 Launch Kick-off call                 Phase 1: Pre Train

9               1                  + Phase 2: System Training                   Phase 2: System Training

10             2                        + Training Protocol Review             Phase 2: System Training

11             3                                 List Candidates                         Phase 2: System Training

12             3                                 Develop Master Schedule      Phase 2: System Training

Hi All, I am trying to find one formula for the “Retrieve Phase Name” column above that will retrieve the text that equals the Task Name from the Level 1 parent associated with that row. It has to be doable, but all attempts to search for this have yielded nada. I'm imagining Parent and/or Ancestor are involved, but... Any hints?

Best Answer


  • Jen Lange
    Jen Lange ✭✭✭✭✭

    Hi @Gregg Peterson. This might be a stupid question, but do you have a "Level" column actually in your sheet? If so, you could use the following:

    =IF(Level@row = "0", "", IF(Level@row = "1", [Task Name]@row, IF(OR(Level@row = "2", Level@row = "3", Level@row = "4", Level@row = "5", Level@row = "6", Level@row = "7", Level@row = "8"), [Retrieve Phase Name]1)))

    It's probably not the most consolidated formula, but it works. Let me know if this works for you.


  • Gregg Peterson
    edited 05/06/21

    Awesome! Thanks Jen and KDM! Yours worked KDM! Now I am going to pull it apart and see how you got it to work! Looking forward to the great learning experience!

    Jen, yours came up as UNPARSEABLE, but I see what you did and love the simplicity. I am going to dig into it and find out, what I'm sure is the small tweak I need to do to get it to work. Another great learning opportunity!

    I do have a Level column with =Count(Ancestors()) BTW.

    This community is the best. I am loving Smartsheet already and this, being my first ask for help, is making me love it all the more! Woo-hoo!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!