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!