Return Ancestor Data by Level Designation

HH_BE1894

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!

Paul Newcome

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.

Paul Newcome

Can you manually enter data in the Year / Month / Qtr columns so we can see the desired output?

HH_BE1894

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!

Paul Newcome

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.

HH_BE1894

That worked, thank you!

Paul Newcome

Happy to help. 👍️