Multi-Select Column Formula to Add Values

mel.greenspan

Is there a way to add values to a multi-select column using a formula? I have several sheets that i want to switch from checkbox columns to a single multi-select column "Subscribed Calendars". It is VERY manual right now doing it by hand and will take a ton of time.

I've tried using the "IF" formula, but it only adds the first true value...which is "Specialty".

=IF(CIA104 = 1, "CIA", IF([Material Planning]104 = 1, "Material Planning", IF([Global Samples]104 = 1, "Global Samples", IF(Jordan104 = 1, "Jordan", IF([Global Merch Ops]104 = 1, "Global Merch Ops", IF([Geo Merch Ops]104 = 1, "Geo Merch Ops", IF([Sports Mktg]104 = 1, "Sports Mktg", IF(Specialty104 = 1, "Specialty", IF(GOLF104 = 1, "Golf", IF(SB104 = 1, "SB", IF(TENNIS104 = 1, "Tennis", "")))))))))))

So for the yellow highlighted cell below, I need it to put in the following values "Specialty", "Golf", "SB" and "Tennis" based on the checkbox columns.

Andrée Starå

@mel.greenspan

Hi Mel,

Try something like this.

Add a new row at the top (Row 1) and add the column names or values that you'd like to use in the Multi-select column. Update the ranges to include all columns that you want to collect values from. Then you'll use the formula below.

=JOIN(COLLECT(ColumnA$1:ColumnC$1; ColumnA@row:ColumnC@row; @cell = 1); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT(ColumnA$1:ColumnC$1, ColumnA@row:ColumnC@row, @cell = 1), CHAR(10))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@mel.greenspan

Hi Mel,

Try something like this.

Add a new row at the top (Row 1) and add the column names or values that you'd like to use in the Multi-select column. Update the ranges to include all columns that you want to collect values from. Then you'll use the formula below.

=JOIN(COLLECT(ColumnA$1:ColumnC$1; ColumnA@row:ColumnC@row; @cell = 1); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT(ColumnA$1:ColumnC$1, ColumnA@row:ColumnC@row, @cell = 1), CHAR(10))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

mel.greenspan

Thank you! Super helpful and it works perfectly.

Andrée Starå

Excellent!

You are more than welcome!

mel.greenspan

@Andrée Starå - While this formula has been working great, I've been having challenges with it always showing as Highlighted Yellow when I turn on changes. However, none of the values are actually changing, but it still is yellow! This is messing with a Smartsheet change log report I use to capture changes from this sheet by filtering based on "modified within last 7 days".

Any ideas to get around this so this formula doesn't auto-update at sporadic times?

Example of cell history:

Andrée Starå

@mel.greenspan

Glad to hear it's working!

Strange with the color.

If you haven’t already, I would recommend that you reach out to the Smartsheet Support Team.

Let me know what you find out.

Kyle Marek

I know this is an older thread, but was very helpful for a similar situation on a new project of mine. One thing that may be helpful with the column formulas, inserting the values into row1 is not valid when converting to column formula; instead I found the easiest solution was to have the values in a separate sheet, and insert the cross-sheet reference for the column names/values.

=JOIN(COLLECT([ColumnValues], ColumnA@row:ColumnC@row, @cell = 1), CHAR(10))

Thank you Andree for this solution