Joining cell's with text contents and checkboxes selections onto one text string

aaboueid

We have a design queue sheet where we receive design requests via a web form. Users make a scope selection using a couple of drop down menus and a bunch of check boxes, and an "other" text box for miscellaneous tasks. On the queue side, the number of columns where this information is gathered occupies quite a large area of the view; please see screen shot. it would be great if there was a way to combine the results of these columns into a single cell with a text strings that represents everything selected on the submittal form.

For example I would like the output from the 3rd row to be:

3D Prsentation, Specifications, Populate Floor Plan

 

And from the 12th row:

3D Sketch, Specifications, no 3d req

 

If the results could be displayed vertically (with a carriage return) that would be even better.

Thanks in advance for any solution or advice.

 

Ayman

 

 

J. Craig Williams

Yes, possible.

Smartsheet does not allow us to reference the column name, so I add a row for that at the top of many sheets (also aids in searches and navigation around big sheets)

Likely COLLECT() and JOIN() will be involved.

For the carriage return, I keep this post bookmarked:

https://community.smartsheet.com/discussion/feature-request-join-carriage-return-separator#comment-13736

Looks like an interesting problem -- an hour or two to refine it.

Good luck.

Craig

JLanders

I had a very similar situation and desired output - For others that come across this, the solution I came up with, as suggested above, included COLLECT and JOIN. 

My situation included only checkbox fields, so the solution below may not work with the 2 text fields above (Rendering and Other). First step was to add a row in line 1, duplicating the column headers since we cannot reference the header names. 

Then in your output column, beginning in row 2, enter:

=JOIN(COLLECT([Space Planning]$1:Other$1, [Space Planning]2:Other2, true), ", ")

If the box is checked, it will return the the column header (from row 1) and enter a comma in between each.

Good luck!