Index(Collection formula for dropdown field with multiple entries

SueinSpain

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

Paul Newcome

What are you expecting for the final output? How are you needing to leverage this multi-select field?

SueinSpain

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 Roadmap

and 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

Paul Newcome

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))

SueinSpain

Apologies Paul I missed your extremely helpful reply.

I'll give it a go and see what I get but sounds about right

Many thanks

Sue