Parent/Child with Nested IF Statement using CONTAINS
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?
Best Answer
-
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.
Answers
-
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 - you are a straight genius, my friend. THANK YOU SO MUCH.
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives