Pulling Parent/Ancestor info depending on row hierarchy


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.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!