Formula to return the level one task name of a subtask

ckcalvins

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!

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=ba27f957afd546419faf3df827991ada

ckcalvins

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

ckcalvins

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

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=ba27f957afd546419faf3df827991ada

ckcalvins

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.

jmyzk_cloudsmart_jp

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