Join Ancestors help
![NickEL](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I have a sheet that has a Task Name column which has Parent and Child rows. I have another column "Levels" that tells me the level that each row sits in the hierarchy. My formula does everything except include the data for task names that are at Level 4. I cannot figure out where I am going wrong. Any help is greatly appreciated.
=IF(Level@row = 2, [Task Name]@row, IF(Level@row = 3, INDEX(ANCESTORS([Task Name]@row), 1) + " - " + PARENT([Task Name]@row), IF(Level@row = 4, INDEX(ANCESTORS([Task Name]@row), 1) + " - " + INDEX(ANCESTORS([Task Name]@row), 2) + " - " + PARENT([Task Name]@row), IF(Level@row = 5, INDEX(ANCESTORS([Task Name]@row), 1) + " - " + INDEX(ANCESTORS([Task Name]@row), 2) + " - " + INDEX(ANCESTORS([Task Name]@row), 3) + " - " + PARENT([Task Name]@row), ""))))
Best Answers
-
Try this instead...
=JOIN(ANCESTORS([Task Name]@row), " - ") + " - " + [Task Name]@row
-
Happy to help. 👍️
To get it working only when another column is checked, you would use it in a straightforward IF statement.
=IF([Checkbox Column Name]@row = 1, original_formula)
=IF([Checkbox Column Name]@row = 1, JOIN(ANCESTORS([Task Name]@row), " - ") + " - " + [Task Name]@row)
Answers
-
Assuming that the "Levels" column is accurate, one way to modify the formula to include level 4 tasks could be to add another nested IF statement to handle that level, like this:
=IF(Level@row = 2, [Task Name]@row, IF(Level@row = 3, INDEX(ANCESTORS([Task Name]@row), 1) + " - " + PARENT([Task Name]@row), IF(Level@row = 4, INDEX(ANCESTORS([Task Name]@row), 1) + " - " + INDEX(ANCESTORS([Task Name]@row), 2) + " - " + PARENT([Task Name]@row), IF(Level@row = 5, INDEX(ANCESTORS([Task Name]@row), 1) + " - " + INDEX(ANCESTORS([Task Name]@row), 2) + " - " + INDEX(ANCESTORS([Task Name]@row), 3) + " - " + PARENT([Task Name]@row), ""))))
This new nested IF statement would go between the existing level 3 and level 5 IF statements, and would handle level 4 tasks by concatenating the first three ancestors of the current task (which should be at levels 1, 2, and 3), along with the parent task name.
Regards
J Tech
If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
-
Hello @J Tech,
I forgot to mention that I have tried the formula change that you mentioned and am still getting the same output.
I do appreciate your help. I am starting to wonder if just having levels 2, 3, & 4 would be better. Perhaps it is level 1 that is causing the issue?
Nick
-
Try this instead...
=JOIN(ANCESTORS([Task Name]@row), " - ") + " - " + [Task Name]@row
-
@Paul Newcome I don't know how this formula worked when the full paragraph one didn't but thank you so much. This is a reminder for me to K.I.S.S. (Keep It Simple Stupid).
-
Please disregard. I figured it out. Again, thank you for all of your help
Also, one more question if I may. Is there any way to get this to only work when a box is checked in another row?
-
Happy to help. 👍️
To get it working only when another column is checked, you would use it in a straightforward IF statement.
=IF([Checkbox Column Name]@row = 1, original_formula)
=IF([Checkbox Column Name]@row = 1, JOIN(ANCESTORS([Task Name]@row), " - ") + " - " + [Task Name]@row)
-
Hello. I am back again. I'm not sure if the correct protocol is to start a new discussion or to continue this one since it builds off of the topics in my OP.
I ran into a problem where the formula returns unparseable if there are only parent and child but no ancestor data. Wouldn't Parent data be considered Ancestor data to the child data? Also, since I am losing the battle to maintain data standardization in my company is there any way to amend the original if statements to search for either [Task Name]@row or [Document Name]@row?
Thank you in advance for your help.
-
Hey @NickEL
Yes, a Parent row is considered an Ancestor. Can you post a screen capture with the formula open in your sheet (but block out sensitive data)?
In regards to flexible column names, there may be a way to do this but the sheet would need to have both Task Name and Document Name as column titles. This is because a formula will error if the column name typed in it doesn't exist - preventing it from reviewing the other statements.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. I was working on this last night and this morning and have figured it out. There was an extra space in the formula. I appreciate your reply as it got me to think things through and check everything again.
-
Glad to hear you were able to figure it out!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!