Can you use Ancestors along with IF(And to check a box?

Hello,

I need some help with a formula, if it is even possible.

Scenario:

Overall Project: checkbox column

Exec Report Activity: checkbox column

Reporting: checkbox column

Is it possible to have a formula search for a checked box in the Overall Project column (Overall Project Check also indicates that it is a parent row) and then look at the children rows in the Exec Report Activity column for any checked boxes, resulting in a check in a new column called Reporting?

I only want a check in the Reporting column if the parent row has the Overall Project column checked and a child row has the Exec Report Activity column checked.

I have been trying variations of this formula without success.

=INDEX(ANCESTORS([Overall Project]:[Overall Project], 1, IF(AND([Exec Report Activity]@row, 1), 1)

Any assistance you can offer is appreciated.

Thank you!

Find more posts tagged with

Accepted answers

Tina

Hi Paul, This formula is exactly what we needed. Thank you so much for your help!

All comments

Danielle Roach

Try:

=IF(AND(Ancestor[Overall Project]@row = 1, Children([exec report activity] = 1), 1, 0)

Tina

Hi Danielle, It didn't work. I tried a couple of variations and still no luck. Thank you

Paul Newcome

Try this...

=IF(AND(PARENT([Overall Project]@row) = 1, [Exec Report Activity]@row = 1), 1)

Tina

Hi Paul, This formula is exactly what we needed. Thank you so much for your help!

Paul Newcome

Happy to help. 👍️

Andrea Mayer

Hi Paul - for this same formula - checkboxes are not being checked for the projects that have checkboxes at levels other than the level directly below the parent. Is there a version of this formula that incorporates ancestors that will search checkboxes at all levels below the parent?

Paul Newcome

@Andrea Mayer Try replacing PARENT with ANCESTORS.

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.