# Join Ancestors help

Options
✭✭

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

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭
Options

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!
• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭
edited 03/20/23
Options

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?

• ✭✭✭✭✭✭
Options

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)

• ✭✭
Options

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.

• Employee
Options

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

• ✭✭
Options

@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.

• Employee
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!