using ancestor to pull multiple levels of the tree structure

Leblanc

for example in this structure below

I would want a formula to be able to pull the level 2-4 info into a column of their own for each level. I can get the level 1 easy enough using the formula: =IF([ancestor helper]@row = 0, [Task Name]@row, INDEX(ANCESTORS([Task Name]@row), 1))

David Tutwiler

Is the idea that on a column in row Task there would be a column that reads "level 2 Level 3 level 4" or is it something different? Sorry, I'm having trouble visualizing what you're looking for.

Leblanc

@David Tutwiler thanks for asking, been out of the office so just circling back to this. I was hoping that including the formula (which has worked well and was referenced in other forum posts) would make it understandable. I just hacked the appearance in a excel:

does that make it clearer?

sharkasits

@Leblanc I did this in one of my sheets. I'm assuming your Ancestor Helper Column is using the following formula

=COUNT(ANCESTORS())

I call that "Place in Hierarchy" in mine. And an a Row column that has the row number. If you're table never has updates in the middle you can use autonumber for that. Otherwise it needs to be more manual. For the Top:

=IF([Place in Hierarchy]@row = 0, [Primary Column]@row, INDEX(COLLECT([Primary Column]:[Primary Column], Row:Row, <Row@row, [Place in Hierarchy]:[Place in Hierarchy], 0), COUNT(COLLECT([Primary Column]:[Primary Column], Row:Row, <Row@row, [Place in Hierarchy]:[Place in Hierarchy], 0))))

For Tier 2:

=IF([Place in Hierarchy]@row < 2, [Primary Column]@row, INDEX(COLLECT([Primary Column]:[Primary Column], Row:Row, <Row@row, [Place in Hierarchy]:[Place in Hierarchy], 1), COUNT(COLLECT([Primary Column]:[Primary Column], Row:Row, <Row@row, [Place in Hierarchy]:[Place in Hierarchy], 1))))

For Tier 3:

=IF([Place in Hierarchy]@row < 3, [Primary Column]@row, INDEX(COLLECT([Primary Column]:[Primary Column], Row:Row, <Row@row, [Place in Hierarchy]:[Place in Hierarchy], 2), COUNT(COLLECT([Primary Column]:[Primary Column], Row:Row, <Row@row, [Place in Hierarchy]:[Place in Hierarchy], 2))))

It's basically looking for the last match for the hierarchy level you're looking for that is above the current row.

Leblanc

@sharkasits I'm getting an unparseable error.

I'm not familiar for how row:row works in this equation.

I also updated the formula to reflect the structure of my doc, so I swapped "place in Hierarchy" with "Tier" and "Primary Column" with "Task Name"

Leblanc

I actually figured it out by accident.

=IFERROR(INDEX(ANCESTORS([Task Name]@row), 2), [Task Name]@row)

where the "2" is the indent level of the task name/primary column