Ancesstors

I can use the below formula to get the ancesstors + task name

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

This will return to me: Parent 1>Parent 2> Parent 3> Task 1

I'd like it to return the reverse order:

Task 1> Parent 3>Parent2>Parent 1

Tags:

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

    You can use COUNT with ANCESTORS to find out how many levels there are, and then use INDEX to identify the ancestor at each relevant positions, 1, 2, 3 etc, and then isolate them and put them back together.

    If you always have the same number of levels, you could use FIND to parse your existing result into four fields, and then concatenate those four back together in your preferred order.

  • Thanks, James,

    What would that look like in this context? I've added a helper column, called Level to make it easy. The helper column now has a level assigned to it. but I am not sure how to formulate the formula to reverse the order

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!