Add function to retrieve values from cell for a multi-select column

Sameer Karkhanis

Like the COUNTM which gives the count of values in a multi-select cell, it would be great if there is any function or combination of functions that can provide an array/list of values from a cell so that any particular value can be compared/matched/processed.

CarlaB

Would LOVE this ability! Prime use case that comes to mind is categorizing subject matter of educational webinars (~200 annually). Some fit under a single medical system or a single species (GI disease in horses), but many fit into multiple (critical care of dogs and cats with renal disease). Being able to accurately categorize these webinars would make it possible to promote the webinar to all members that would be interested instead of having to decide between criticalists or nephrologists.

jmyzk_cloudsmart_jp

Hi @CarlaB

I also love @Sameer Karkhanis 's idea of "a function or combination of functions that can provide an array/list of values from a cell."

Until such a function or combination of functions is available, here is a solution to split the sizeable multiple dropdown list values.

Dropdown List Sheet

First, we need to create a list of multiple dropdown lists. You can directly generate the list sheet if you have a definite list as the multiple dropdown list.

The demo below explains the method to create the list from the target sheet.

https://app.smartsheet.com/b/publish?EQBCT=74d82034d30344a9808d2759982a44ac

The above Dropdown List sheet has a Sheet Summary filed, Summary List, which lists all the available lists from the target sheet.

[Summary List]=JOIN({Multiple Dropdown List}, CHAR(10))

Then, in the first row, the formulas for [List -1] and [List] are;

[List -1]=[Summary List]#

[List] =IF(FIND(CHAR(10), [List -1]@row) = 0, [List -1]@row, LEFT([List -1]@row, FIND(CHAR(10), [List -1]@row)))

The first formula is a cell formula to avoid circular reference errors.

The cell formula for [List -1] from the 2nd row is;

[List -1]=IFERROR(SUBSTITUTE([List -1]1, List1, ""), "")

Split Multiple Dropdown List Sheet (target sheet)

Cell formula version

https://app.smartsheet.com/b/publish?EQBCT=d212ee35c1994e52871737e87672b8b5

In the cell formula version, the first row has an index number from 1 to 50 or whatever number you want to split the value into.

Then, the cell formula to split the value is;

[L1]=IFERROR(INDEX(COLLECT({List}, {List}, CONTAINS(@cell, $[Multiple Dropdown List]@row)), [L1]$1), "")

The COLLECT function returns a range or array/list of values from a cell in the [Multiple Dropdown List] column. The [L*]$1 provides the row index for the INDEX function.

Column formula version

This column formula version has a hard-coded row index in the formula;

[L95]=IFERROR(INDEX(COLLECT({List}, {List}, CONTAINS(@cell, [Multiple Dropdown List]@row)), 95), "")

https://app.smartsheet.com/b/publish?EQBCT=4505ab5abea54e4fb813e7bddf3c7e85

If you need a copy of the solution, please get in touch with me.

CarlaB

Thanks! @jmyzk_cloudsmart_jp I'll give that a try. That looks like that could do the job…. Thanks for taking the time.