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

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

Find more posts tagged with

Forms

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.