Index(Collection formula for dropdown field with multiple entries
Hi Team,
I know I am asking a lot but if anyone knows it will be this group :)
I have a dropdown field that allows multiple entries - for example sales blockers
These blockers can be grouped together to give a blocker group.
I've used =INDEX(COLLECT before but not sure it will work for multiple entries
any ideas if I can even do this ???? if so how on earth would I make it work
Many thanks
Sue
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
Answers
-
What are you expecting for the final output? How are you needing to leverage this multi-select field?
-
so I have a list of blockers in a dropdown field with multiple values allowed but only from the dropdown list e.g. Accounting Integration
AI
Alert Triggers
API
API Add On
BI Reporting
Cost
Custom Tick Boxes
Data Migration
Dev Roadmapand to date a record has selected 17 of them !!! the example here has 3
I then have a field called Blockers group and ideally I wanted to do an INDEX(COLLECT to a smartsheet that tells me what the blocker group is for each blocker so in this example by coincidence they all belong in the General group.
I think I have probably solved it, as long as my user only wants numbers, to do it using summary fields - so happy to close this request as jsut being too much
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
This can certainly be done if I understand what you need correctly.
You want to take all selections from the [Blockers] column and output a group or groups based on those selections made.
My suggestion would be a helper sheet that has all blockers listed in one column and their group in another column. Then you would use a JOIN/DISTINCT/COLLECT combo to grab each of the appropriate groups like so:
=JOIN(DISTINCT(COLLECT({Reference Table Groups Column}, {Reference Table Blockers Column}, Has(Blockers@row, @cell))), CHAR(10))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!