List an ancestor value if a child meets a condition
I am trying to write a formula to return the value of an ancestor if a checkbox cell is checked.
For context, I need to see if the ancestor and all child cells are relating to an event. The sheet has a mix of events and other projects, and there is a checkbox column to mark if it's an event. The checkbox is checked at the row of the event, which will usually be called 'event' not the actual name (eg. Christmas Party).
How can I write a formula to check if a row's child cells has the event checked, and if so return the ancestor project name?
Answers
-
If the ancestor is one level above, you can do something with the IF and PARENT function. If it is a grandparent or further removed, you will need a bit more work. For now, I will assume it is a parent.
-
Sorry ^ The community is glitching and my comments are vanishing before my eyes leaving only the option to post. I will continue….
I think this is what you are looking for
-
The ancestor is two, sometimes three levels above
-
If the checkbox is checked return the details from the parent row in the primary column. The formula is
=IF(checkbox@row = 1, PARENT([Primary Column]@row))
If this is not what you need, or you need a grandparent row, let us know.
-
Thank you, the project name is usually two levels above, sometimes more. If I use ANCESTORS it doesn't seem to work.
-
Are you able to provide some screenshots for context?
-
Hi Paul, sure thing. This is the screenshot of the sheet, I am trying to write a formula to sit in another sheet (summary & metrics focused) that will let me create reports and dashboards to share with stakeholders.
-
Try this:
=IFERROR(INDEX(ANCESTORS([Primary Column]@row), 1), [Primary Column]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!