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

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?