Trying to JOIN/COLLECT across several multi-select columns in a different sheet

I've spent a fair bit of time trying to figure this out with no luck. I suspect at least part of it can't be done with multi-select columns but I'm sure some of you good people can school me on this.

I'm trying to join the values of 6 multi-select columns on one sheet into a single column on a separate sheet. Cells can be blank, and I want to leave the blanks out of the JOIN/COLLECT.

The ideal result would be one cell on the target sheet that lists all of the values from the 6 multi-select columns in the corresponding row of the source sheet; and it lists them as multi-select values (in the gray bubbles).

I've created two sample sheets with just three multi-select columns to try and make it easier to help me:

On the source sheet you can see in the "Combined" column that I'm able to JOIN/COLLECT all the values from the Color, Sheen and Coats column.

Formula:

=JOIN(COLLECT(Color@row:Coats@row, Color@row:Coats@row, NOT(ISBLANK(@cell))), ", ")

For multiple values in one column, the "Combined" column correctly displays them as separate multi-select values, but when it's combining across columns it inserts the ", " and treats the two values as one multi-select value. So, my questions here are:

Can I combine all the values across those three columns and display them all as separate multi-select values in the "Combined" column
Is there any way to make it display the combined values in the order they appear across the columns, rather than alphabetically?

Moving to the Target Sheet, I'm trying to do the JOIN/COLLECT by referencing the Color, Sheen and Coats column as a single range and then matching the row on the Room column.

Formula:

=JOIN(COLLECT({Paint Details}, {Room}, Room@row, {Paint Details}, NOT(@cell = "")), ", ")

This gets me an "Incorrect Argument Set" error. Is it even possible to do what I want with a cross-sheet reference, or is there just a problem with my formula? If it's not possible then I can always just pull the "Combined" column from the source sheet into the target sheet.

Find more posts tagged with

Formulas

Accepted answers

Chris Russell

Just to circle back on this. I ended up spending an hour and a half on the phone with support. The rep said there were four Smartsheet people working on it while we were on the call. This is the solution they gave me, which seems to work:

=JOIN(COLLECT({Color}, {Room}, Room@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Sheen}, {Room}, Room@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Coats}, {Room}, Room@row), CHAR(10))

So you have to do a JOIN/COLLECT on each individual column rather than across a range of columns, and then manually tie them together with CHAR(10). Don't forget that the column with this formula also has to be a multi-select dropdown.

All comments

Andrée Starå

Hi @Chris Russell

I hope you're well and safe!

Try replacing the delimiter "," with CHAR(10) instead.

Did that work/help?

If not, can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chris Russell

Hi Andrée,

Thank you very much! The CHAR(10) solution works to make all the JOIN/COLLECT values show up as individual multi-select items.

It does not help with the "Invalid Argument Set" error when trying to do the JOIN/COLLECT from a different sheet.

In my original post I did link to sample source and target sheets:

The "Invalid Argument Set" error appears on the target sheet, where I'm trying to do the JOIN/COLLECT on columns in the source sheet. Let me know if you have any thoughts on that.

Kelly Moore

Hey @Chris Russell

I haven't gotten around the Invalid Argument Set error yet, however, as a work around until someone offers that solution, can you join on your source sheet into a helper column then INDEX/match that joined cell into your target sheet?

=INDEX({Joined column from source sheet}, MATCH(Room@row, {Room in source sheet}, 0))

The column in the target sheet must be formatted as a multiselect dropdown to match the Joined column formatting in the source sheet.

Would that work - at least short term?

Chris Russell

Hi @Kelly Moore,

Thanks for the suggestion. I'm already doing something similar, just using JOIN/COLLECT instead of INDEX/MATCH. I can live with this if the cross-sheet approach just won't work.

Andrée Starå

@Chris Russell

Happy to help!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chris Russell

=JOIN(COLLECT({Color}, {Room}, Room@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Sheen}, {Room}, Room@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Coats}, {Room}, Room@row), CHAR(10))

PeggyLang

@Andrée Starå
I have a very similar need. I need to join/collect all the store #'s where 'Angerico' is the Supervisor into a cell that is multiselect drop down.

In my target sheet I want to be able to type 'Angerico' in supervisor column and in the adjacent column I wish all of his stores to populate in the cell.

I'm stuck.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.