Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Formula to return the level one task name of a subtask

    ✭✭✭
    in Formulas and Functions

    I'm having trouble figuring out how to create a formula that always returns the top-level (level 1) task of the subtask underneath. I've been able to return a parent, which works for level 2, but once I get to level 3, it'll return the parent as level 2. I want level 1 for this new column.

    Line 406 is a good example, as it's returning the level 2 parent task name. My new column, I'd want to return "Overrides" or the task in row 401, which is the top task within the hierarchy.

    Thanks in advance for some help here!

    image.png
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      edited 07/08/24 Answer ✓

      Hi @ckcalvins

      You can combine INDEX and ANCESTORS functions to achieve your goal. Here's a formula you can use:

      =IF([Subtask Identifier]@row, INDEX(ANCESTORS([Task Name]@row), 1))

      As demonstrated in the "JOIN JOIN ANCESTORS" column of the attached Smartsheet image below, the ANCESTORS function retrieves all the ancestor tasks, starting from Level One up to the parent task. By using row_index 1 with the INDEX function on the ANCESTORS range, you can specifically return the Level One task.

      Site faviconSmartsheet

      image.png
      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭

      I think I figured it out…

      =IF([Task Level]@row = "One", [Task Name]@row, INDEX([Task Name]:[Task Name], MATCH("One", [Task Level]:[Task Level], 0)))

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Actually this formula doesn't "always" work as it's matching to the nearest. Anyone have any ideas?

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 07/08/24 Answer ✓

      Hi @ckcalvins

      You can combine INDEX and ANCESTORS functions to achieve your goal. Here's a formula you can use:

      =IF([Subtask Identifier]@row, INDEX(ANCESTORS([Task Name]@row), 1))

      As demonstrated in the "JOIN JOIN ANCESTORS" column of the attached Smartsheet image below, the ANCESTORS function retrieves all the ancestor tasks, starting from Level One up to the parent task. By using row_index 1 with the INDEX function on the ANCESTORS range, you can specifically return the Level One task.

      Site faviconSmartsheet

      image.png
      · Share on FacebookShare on Twitter
    • ✭✭✭

      Amazing, thank you @jmyzk_cloudsmart_jp! Worked perfectly and was exactly what I was looking for.

      Just so I know, what was the formula you had in there on your sheet for "JOIN ANCESTORS" column? Curious to see it.

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 07/08/24

      Hi @ckcalvins

      You can click the "Smartsheet" button to access the published demo sheet, then click the fx to check the column formula.😁

      =JOIN(ANCESTORS([Task Name]@row), ", ")

      Found this helpful? Upvote it as 'Insightful / Awesome'!

      Screenshot 2024-07-09 05.20.41.png
      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions