Attempting to convert data from check boxes to Multi Select Dropdown

Thank you in advance for any help you can provide.

Since the Multi Select Dropdown option was not around when we created our form, we used a variety of check boxes to gather the required data via a form published on our website.

Now that the Multi Select Dropdown is an option, we would like to migrate that data over to a single column, instead of having 9 columns of check boxes, but we are looking at about 3000 rows.

I was hoping there was a simple formula to do this, without having to use the API and blow the dust off my pyhton textbooks from college.

I have not been able to figure out anything or find anything to make this work, does anyone know if there exists a formula that can possibly take 8 columns of check boxes, and use them to duplicate the necessary checks into a multi select dropdown menu?

Find more posts tagged with

Human Resources

Sheets

API and Developers

Formulas

Comments

Andrée Starå

Hi Kathy,

Interesting!

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

3BKathy

Thanks Andrée,

I am going to post the screenshot here then I will also Email one to you.

Andrée Starå

Happy to help!

I'll take a look and get back to you!

3BKathy

Also, an update.

I know I can use IF to check a condition on one of the check boxes, then add the entry of that check box to the multi select drop down.

=IF(COUNTIF([Construction Manager]@row, 1), "Construction Manager")

a couple issues with this:

----I can only use one IF as far as I can tell. I can nest them, but that would require me mapping out all possible combinations (2^9 possible combinations = 512 combinations) and only triggering the value_if-true action on the combination that is true.

----I can not figure out how to set multiple values in the box from the IF. example here we have "Construction Manager" and what get applied if our condition is true, but if I change that and add behind it "Utility Test" it becomes unparsable, if it try a concatenation "Construction"+"Utility" then the multi dropdown has ConstructionUtility as one entry, I can not figure out how to apply mulitple entries from the IF.

Andrée Starå

Glad we got it working!

Have a fantastic week!

Best,

Andrée

Andrée Starå

For reference, here's how we solved it.

Try something like this.

=JOIN(COLLECT([Construction Manager]$1:[Coating Inspector]$1; [Construction Manager]@row:[Coating Inspector]@row; @cell = 1); CHAR(10))

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

=JOIN(COLLECT([Construction Manager]$1:[Coating Inspector]$1, [Construction Manager]@row:[Coating Inspector]@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.

I hope that helps!

Best,

Andrée

Andrée Starå

Separate screenshot because of Cloudflare.

Johnny Addison

I'm having similar issues, but through a different avenue. I've got a Wufoo Form connected to a Smarsheet Grid via a Zapier Zap. In the past, I always had to just break the Form checkboxes down to individual Smartsheet columns, but with the new MultiSelect option, I'm trying to put this particular Form checkbox field into a single column.

After the initial setup, I tested and received the following error message in Zapier:

"We had trouble sending your test through.The app returned "Value is not supported for this column type. Use objectValue instead.". This usually happens when your Zap is missing a required field or a field value isn't in a recognized format."

The drill-down detail gave me the following message:

"We made a request to api.smartsheet.com and received (400) Bad Request."

As a troubleshoot, I changed the Smartsheet column type from Dropdown (Multi-Select) to Text/Number and, predictably, that allowed the data to come through.

I tried a number of other pseudo-format changes in Zapier (adding and removing spacing between the Wufoo field multi-select data points, adding and removing quotes) all to no success.

The last troubleshoot was adding data to the Multi-Select cell in Smartsheet and exporting to see how the data translated to excel. With that, it looks like each, separate Multi-Select option is differentiated by a line break. Is this the key or am I going down another troubleshoot 'rabbit hole'?

Sorry for the long-winded comment and thanks in advance!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…