i have a form that has countries and the building names for each country. I only have one field in

Options

I have a sheet which has 2 fields - Country and Building names (by Country). The form has the two fields - country and Building names. I want to be able to have the respective Building names as a dropdown based on which country was chosen on the form and have all choices for buildings get posted to the ONE field for Building Names on the base sheet

Best Answer

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Gita Mooney

    One way you could do this is to use helper columns, Form Logic, and a nested IF column formula in the final Building names column.

    Here's the set up: On the Sheet you have a column for the Country and then other columns, 1 for each Country's Buildings as droplists with the options. Then you have a final "Building" column. On the Form you set it up so when you choose CountryA, the form logic only shows the column/field for CountryA's Buildings. After you set that up for all the columns, you go to the final "Buildings" column and do a nested IF statement like: =IF(CountryA's Buildings@row<>"",CountryA's Buildings,IF(CountryB's Buildings@row<>"",CountryB's Buildings,...etc. Then hide all the Country's Buildings columns.

    That formula is saying that if CountyA's Buildings is not blank, show that value, if it is blank, then check CountryB's Buildings...etc.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Gita Mooney

    One way you could do this is to use helper columns, Form Logic, and a nested IF column formula in the final Building names column.

    Here's the set up: On the Sheet you have a column for the Country and then other columns, 1 for each Country's Buildings as droplists with the options. Then you have a final "Building" column. On the Form you set it up so when you choose CountryA, the form logic only shows the column/field for CountryA's Buildings. After you set that up for all the columns, you go to the final "Buildings" column and do a nested IF statement like: =IF(CountryA's Buildings@row<>"",CountryA's Buildings,IF(CountryB's Buildings@row<>"",CountryB's Buildings,...etc. Then hide all the Country's Buildings columns.

    That formula is saying that if CountyA's Buildings is not blank, show that value, if it is blank, then check CountryB's Buildings...etc.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!