Help with Ancestors forumla to show Parent Row information
Hello Smartsheet Community
Stuck with using Ancestors based formula to do the following:
- 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.
- 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!
Answers
-
Try this...
=IFERROR(INDEX(ANCESTORS([Task Name]@row), 1), [Task Name]@row)
-
Thank you Paul! Worked sphleendily
-
Happy to help. 👍️
-
@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!
-
@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.
-
@Paul Newcome Ahhh! Yes! I changed it to '2' and it worked like a charm. Thanks a million!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!