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?

Find more posts tagged with

Accepted answers

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.