Return Ancestor Data by Level Designation
I'm logging multiple years' worth of data into a single sheet that is organized by year, quarter and month at Levels 1, 2 and 3. I believe I need to use the ancestors function along with index (maybe?) but I can't seem to find a formula that works. A snapshot of my table is below. In words, I want my Year column to return the Project@Row at its Level 1 ancestor. Qtr should return Project@Row at Level 2 and Month Project@Level for Level 3. Level 1 should just reflect Project@Row. Seems like it should be easy but I'm stuck. Thanks in advance!
Best Answer
-
In that case you would use something like this:
=IFERROR(INDEX(ANCESTORS(Project@row), 1), Project@row)
You just need to change that 1 to a 2 or 3 as needed.
Answers
-
Can you manually enter data in the Year / Month / Qtr columns so we can see the desired output?
-
Sure, here is what I'd like Year, Qtr and Month to return in those columns based on the Level and Project organization menu. Thanks!
-
In that case you would use something like this:
=IFERROR(INDEX(ANCESTORS(Project@row), 1), Project@row)
You just need to change that 1 to a 2 or 3 as needed.
-
That worked, thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!