Join Ancestors help

NickEL

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

Paul Newcome

Try this instead...

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

Paul Newcome

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)

J Tech

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.

NickEL

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

Paul Newcome

Try this instead...

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

NickEL

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

NickEL

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?

Paul Newcome

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)

NickEL

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.

Genevieve P.

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.

NickEL

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

Genevieve P.

Glad to hear you were able to figure it out!