Pulling Parent/Ancestor info depending on row hierarchy

Lamont_norberto

I'm trying to have a column (Dept Helper) auto fill based on one Ancestor cell in another column (Department). I have a helper column (APC) keeping track of the hierarchy already (Ancestor = 0-Anc, Parent=1-Par, Child=2-Chi). I only want the Dept Helper to fill-in rows that are not the header row (Ancestor).

Here is the formula I have now, I can't figure out why it shouldn't work.

=IF(APC@row = "1-Par", PARENT(Department@row), IF(APC@row = "2-Chi", ANCESTORS(Department@row)))

It works for Parents but not Children, which returns "INVALID COLUMN VALUE"

I've tried using OR and NOT, I'm not entirely sure I used them properly, but they haven't worked either.

Any help is appreciated.

Thanks

Paul Newcome

Try this:

=IF(APC@row <> "0-Anc", INDEX(ANCESTORS(Department@row), 1))

Paul Newcome

Try this:

=IF(APC@row <> "0-Anc", INDEX(ANCESTORS(Department@row), 1))

Lamont_norberto

Perfect, that did the trick. Thank you!

Paul Newcome

Happy to help. 👍️