Join Ancestors help

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:

Best Answers

Answers

  • J Tech
    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.

    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!
  • NickEL
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this instead...

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

  • NickEL
    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
    NickEL ✭✭
    edited 03/20/23

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    Glad to hear you were able to figure it out!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!