Is there an easier way to automate multiple columns from a single multi-select dropdown?

Monique Odom

One of my teams uses a sheet to track to the status of products in every state, with a column containing a status dropdown list for each state (Requested, In Progress, On Hold, Completed, etc.). We recently implemented a form for our internal clients to submit requests. As part of this form, we have two questions pertaining to the states: 1) Are you requesting all 50 states? (Yes/No) radio buttons, and 2) If not, select the states you're requesting: (this is a multi-select dropdown).

My goal: When a form is submitted, the sheet will automatically change the states being requested to "Requested". This is easy for the "All 50 States" selection - a single automation. However, for the multi-select dropdown, I'm having to do a separate automation for each of the 50 states because a single automation has a limited number of conditions allowed. The thing making this frustrating is that the cells needing to show as "Requested" are dropdowns that the team needs to be able to update later on. These columns for the state statuses are already built into reports and dashboards.

Is there an easier way to do this? I feel like I'm missing something obvious here, or there's a different way to think about this altogether.

Example of the result I'm looking for, with the state columns being a single select dropdown:

James Keuning

Is there a reason why you are using columns for this data, instead of rows? I assume there is some sort of product ID here somewhere, so your data could be organized like:

ProductID,State,Status

001,TN,Requested

001,TX,Requested

001,UT,Sent

002,TN,Requested

002,WV,Sent

I'm not saying I have an answer if you pivot your data, but I'd suggest that having the state name as the column name is not a normalized data structure. So perhaps you use one structure to collect the information from the form, and then you pivot the data for your users.

Monique Odom

The simple answer: That's how they had their sheet set up before I came on board. Was wondering if there was a way for me to automate with their current setup and workflows, but I think it might be more useful in the long run if I recreate it in a row structure as you're saying, potentially with parent/child relationships for products and states.

A follow-up question, then: If I have a form for our clients to request a single product, selecting on that form which states they'd like to be included, how could I set it up so that the single form will result in separate rows for each state indicated for that product? I'm assuming I will need a helper sheet, since automating multiple rows is not a current function with the use of forms.

For example:

Form - Request Product A, states MI, WV, FL, GA, CA, OH

Sheet -

Parent Row - Product A information

Child Row - MI - Requested, additional info

Child Row - WV - Requested, additional info

Child Row - FL - Requested, additional info

Child Row - GA - Requested, additional info

Child Row - CA - Requested, additional info

Child Row - OH - Requested, additional info

Paul Newcome

Having a single form entry populate multiple rows is not possible at this time.

Unfortunately I am thinking that setting up the individual automations is the only option at this time.