Retrieve Level 1 Text Values

Gregg Peterson

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?

Kelly Moore

Hey @Gregg Peterson

Here's an alternative approach to Jen's skillful formula.

=IF(Level@row = 1, [Task Name]@row, IF(Level@row > 1, INDEX(COLLECT(ANCESTORS([Task Name]@row), ANCESTORS(Level@row), 1), 1), "NA"))

I also assumed you had the [Level column] in your sheet. If not, please add it as a helper column using this formula =Count(Ancestors())

cheers

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.

-Jen

Kelly Moore

Hey @Gregg Peterson

Here's an alternative approach to Jen's skillful formula.

=IF(Level@row = 1, [Task Name]@row, IF(Level@row > 1, INDEX(COLLECT(ANCESTORS([Task Name]@row), ANCESTORS(Level@row), 1), 1), "NA"))

I also assumed you had the [Level column] in your sheet. If not, please add it as a helper column using this formula =Count(Ancestors())

cheers

Gregg Peterson

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!