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
Best Answer
-
Try this:
=IF(APC@row <> "0-Anc", INDEX(ANCESTORS(Department@row), 1))
Answers
-
Try this:
=IF(APC@row <> "0-Anc", INDEX(ANCESTORS(Department@row), 1))
-
Perfect, that did the trick. Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!