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.

Thanks

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

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

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.