COLLECT not looking within multiselect criterion range
I'm using a combination of JOIN and COLLECT to get some data, but the COLLECT is only looking at the first value in my Multiselect column for the criterion range.
I have a sheet that lists value similar to below:
Name Category
A 1,2
B 2,3
C 1,3
What I'm trying to do is have another sheet display it this way:
Category Name
1 A,C
2 A,B
3 B,C
My current formula is only displaying it this way:
Category Name
1 A,C
2 B
3 <Blank>
Any suggestions?
Best Answer
-
That's because I missed a portion of the CONTAINS function. My apologies.
=JOIN(COLLECT({Name Range}, {Category Range}, CONTAINS(Category@row, @cell)), ", ")
Answers
-
Can you provide your current formula?
Something like this should work...
=JOIN(COLLECT({Name Range}, {Category Range}, CONTAINS(Category@row)), ",")
-
=JOIN(COLLECT({Range 1}, {Range 2}, Objective@row), ", ")
Basically the same as your example, but whenever I use the CONTAINS(), I get an "INVALID OPERATION"
-
That's because I missed a portion of the CONTAINS function. My apologies.
=JOIN(COLLECT({Name Range}, {Category Range}, CONTAINS(Category@row, @cell)), ", ")
-
That did it! Thank you so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!