Help with Ancestors forumla to show Parent Row information

bhuyler

Hello Smartsheet Community

Stuck with using Ancestors based formula to do the following:

1. In the process of building a sheet to roll up task and eventutally project information so that as we decompose/create more children from the parent row, the parent task from the parent row shows up under the parent task column. So in this example, task #2 and task #3 will always have a reference back to Task #1 as the parent.
2. The screen shots below show the INDEX(ANCESTORS...) formulas I've been using which based on what I've found so far in the SS community, should work (?). What are am I missing?

Thanks for any help, this community is AWESOME!

Paul Newcome

Try this...

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

bhuyler

Thank you Paul! Worked sphleendily

Paul Newcome

Happy to help. 👍️

Lorraine Stevens

@Paul Newcome Hi Paul! I searched for a similar scenario and came across your answers on this one.

I am trying to pull in the phase on a sheet for child tasks into another column. I have a "Level" helper column and the phases are listed as level 1s. I tried to use the formula above but it brings in the level 0 name as expected. Is there a way I could also include level 1 as criteria.

In the attached screenshot example, I'd like the Phase column in rows 3 - 14 to be "Upon Request Phase".

Thank you for your help!

Paul Newcome

@Lorraine Stevens The way this formula works is we specify which ancestor we are pulling in (number in bold).

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

If that number is 1 then we are pulling in the first ancestor. If we want to pull in the second ancestor, then you should be able to just change that number to 2.

Lorraine Stevens

@Paul Newcome Ahhh! Yes! I changed it to '2' and it worked like a charm. Thanks a million!

Paul Newcome

@Lorraine Stevens Happy to help. 👍️