Autopopulate multiple columns based on the selection of one drop down?

Brandon R

I'm creating a Form for my end users and would like to pull back information on specific stores. I have 246 stores with addresses. I have these columns that will need to populate data based on the option selected in the Store # (Drop Down):

Like I said above, I have 246 different stores that will each have their Store Names, Addresses, etc. so a NestedIF statement will be very lengthy. What is the best approach to building this out on either the sheet itself or through using Logic when building a Form?

juliesilverio

Hi Brandon,

I would approach this utilizing INDEX/MATCH formulas to pull your data into your form responses. In order to do so, I would set up a separate Smartsheet (if you don't already have this) that includes all 246 store addresses with the included columns from your screenshot.

Then, you can have people select your store name from a dropdown in your form and utilize the INDEX/MATCH to pull the rest of the required information over from your separate Smartsheet.

Here is an article in Smartsheet that explains how to set up those formulas based on your needs: https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations?_gl=1*1cj09q9*_ga*MTYzMjYzMDQ3OS4xNzAxMzAyOTQ3*_ga_ZYH7XNXMZK*MTcwNTYwNzE0Ny4yMS4xLjE3MDU2MTA2ODYuNjAuMC4w&_ga=2.60750893.1436455242.1705514304-1632630479.1701302947

I would utilize the first section, "Lookup one cell using a matching value" and create a column formula for your Address, City, State, etc. columns.

Here is an example of the formula in Lehman's terms:

=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; {ColumnWithTheValueToMatchAgainstTheCell}; 0))

(I Borrowed this explanation from @Andrée Starå -- it's very helpful to conceptualize what data you need to input!)

Hopefully this helps!

juliesilverio

I had some additional free time at the end of my day so I wrote the example formulas out for you. You'll want to enter and adjust this formula in your Form Responses and reference your other sheet. Below is the "Address" column example:

=INDEX({Separate Sheet Address Column}, MATCH([Store Name]@row, {Separate Sheet Store Name Column}, 0))

The same logic will apply across your other columns, as well.

Brandon R

I've handed this out over to a colleague. I've shared this post with him and he's going to try what you've suggested. I'll follow up with his results once he's finished.

Thank you!

juliesilverio

@Brandon R Of course! Let me know if your colleague struggles with setting it up, happy to walk through it and pay it forward since someone helped me learn this process. :)