Parent/Child with Nested IF Statement using CONTAINS

Danielle Arteaga

I have a multi-select dropdown column in my worksheet called "Interdependencies." The sheet has parent rows that cover a main area with various child rows that cover discrete tasks.

The dropdown options are:

Learning Offers & Pathways

B2C Partnerships

Student Success Phase 1

Credentials + Employment

Marketing & Demand Gen

B2B2C Partnerships & GTM

B2B reimagined on SGP

B2B Growth

Engineering

Other

For each discrete task (on the child rows), the user selects one or more of the above options for the tasks.

I want the parent row to "roll-up" the selected options just once. To do that, I wrote this formula:

=IF(CONTAINS("Learning", CHILDREN(Interdependencies@row)), "Learning Offers & Pathways", IF(CONTAINS("B2C Partnerships", CHILDREN(Interdependencies@row)), "B2C Partnerships Content & Services", IF(CONTAINS("Student Success", CHILDREN(Interdependencies@row)), "Student Success Phase 1 - Research", IF(CONTAINS("Credentials", CHILDREN(Interdependencies@row)), "Credentials + Employment", IF(CONTAINS("Marketing", CHILDREN(Interdependencies@row)), "Marketing & Demand Gen", IF(CONTAINS("B2B2C", CHILDREN(Interdependencies@row)), "B2B2C Partnerships & GTM", IF(CONTAINS("B2B reimagined", CHILDREN(Interdependencies@row)), "B2B reimagined on SGP", IF(CONTAINS("B2B Growth", CHILDREN(Interdependencies@row)), "B2B Growth", IF(CONTAINS("Engineering", CHILDREN(Interdependencies@row)), "Engineering", "Other")))))))))

But, of course, the formula works ONLY UNTIL THE first condition is met, then it stops analyzing. I don't want it to stop, though. I want it to look for an instance of EVERY item in the drop-down menu.

I think this is accomplished with an "OR" somewhere, but I can't quite get the syntax right. Might need to use a COUNTIFS instead?

Paul Newcome

If it is a multi-select dropdown, you can use

=JOIN(CHILDREN(), CHAR(10))

Using the CHAR(10) - line break - delimiter in a multi-select dropdown column will automatically remove duplicates, so it should only populate one instance of each selection across the child rows.

Paul Newcome

If it is a multi-select dropdown, you can use

=JOIN(CHILDREN(), CHAR(10))

Using the CHAR(10) - line break - delimiter in a multi-select dropdown column will automatically remove duplicates, so it should only populate one instance of each selection across the child rows.

Danielle Arteaga

@Paul Newcome - you are a straight genius, my friend. THANK YOU SO MUCH.

Paul Newcome

Happy to help. 👍️