Formula to return the level one task name of a subtask
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!
Best 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.
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))) -
Actually this formula doesn't "always" work as it's matching to the nearest. Anyone have any ideas?
-
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.
-
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.
-
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'!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!