Is it possible to have a drop down list based upon criteria from another column?

Protonspounge · 10/13/23

In my primary column, I have a list of countries and in the "Type of Change" column I have a drop down list from which the user will select an option specific to that country.

I would like to ask if it is possible to have the drop down list only present the options based upon the country in a given row.

For example, if I selected the row for Switzerland, can I restrict the values in the dropdown box to only show options relevant to Switzerland?

Protonspounge · 05/17/24

My query above was solved with a really nice solution that worked for me. Not exactly a dynamic drop down list but something close enough for my needs and a solution that requires minimal maintenance.

It involved making a separate {Database Sheet} with a list of options for each row in my {Working Sheet}. In my case, I was focusing on countries/regions on each row and so for each [Country Region] in my {Database Sheet}, I had up to 3 options detailed in [Option 1], [Option 2], [Option 3].

(Image below is an example of {Database Sheet})

Then in my {Working Sheet}, I had a column matching the [Country Region] column in the Database sheet. (See 1 in the image below)

In my {Working Sheet}, I have an [Options] column with a formula to pull in each of the options for that country from the {Database Sheet} and make them available to read in the sheet. (See 2 in image below)

Formula =INDEX(COLLECT({Database Sheet_Option 1}, {Database Sheet_Country}, [Country]@row), 1) + CHAR(10) + INDEX(COLLECT({Database Sheet_Option 2}, {Database Sheet_Country}, [Country]@row), 1) + CHAR(10) + INDEX(COLLECT({Database Sheet_Option 3}, {Database Sheet_Country}, [Country]@row), 1))

Again, in the {Working Sheet}, I have a dropdown [Choose Option] column with 1, 2, 3 as options to select from. (See 3 in the image below)

Finally, there would be a [Decided Option] column that would check this and pull in the relevant selected option for that country (See 4 in the image below)

Formula =IF([Choose Option]@row = 1, INDEX(COLLECT({Database Sheet_Option 1}, {Database Sheet_Country}, [Country]@row), 1), IF([Choose Option]@row = 2, INDEX(COLLECT({Database Sheet_Option 2}, {Database Sheet_Country}, [Country]@row), 1), IF([Choose Option]@row = 3, INDEX(COLLECT({Database Sheet_Option 3}, {Database Sheet_Country}, [Country]@row), 1))))

(Image below is an example of {Working Sheet})

This solution was perfect for my use case. It came with the benefit that any updates made to the {Database Sheet} would automatically be reflected in my numerous {Working Sheet}.

In respect of credit where credit is due - I did not come up with the solution, the idea and concept came from an employee at Irlca Limited (enquiries@irlca.com)

Jon Barto · 10/13/23

I am looking for something very similar. Except have a master sheet that holds the dropdowns. I have recently posted about this as well. If anyone has a solution please let me know as well. We would be willing to pay someone or company to build this out for us.

isabonita · 10/13/23

It is POSSIBLE - but the information has to be entered through a Form or a Dynamic View Details Panel.

It's not a pretty solution - but here goes:

You need a column for EACH option for the drop downs: (e.g. one for Switerland, one for UK, etc.)
Load the appropriate information in each one
In a Form (for only new entries) or Dynamic View Panel (for new or modified) - have the user select the main item (Switerland or UK) and then use Logic to display the correct drop-down column.
You then need a column with a formula that will hold the selected value (to make reports simpler). (My formula is pretty simple: =IF([Switzerland Dropdown]@row <> "", [Switerland Dropdown]@row, IF(UK Dropdown]@row <> "", [UK Dropdown]@row, "")

I normally add in an automation to clear out the inapplicable values in the case when the dropdown value is saved from the wrong sub-column.

For a little more clarity the dropdown area of my sheet is something like this:

Country. Dropdown Info (with column formula) Switzerland Dropdown UK Dropdown

P.S. I was new to Smartsheet when I first asked everyone I could think of about this and in searching I was told I had to have Data Shuttle to make this work - but you don't - if this is its own sheet and isn't being replicated all over the place. You can just set up the specific Drop Downs manually, and then you will need to remember to update them if information changes. And - even if it is being replicated - just know that it would need to be manually changed.

isabonita · 10/13/23

@Jon Barto - I would be happy to show you how to do this if you would like... no need to pay me.. with the master sheet - if you do have Data Shuttle - it's not difficult... just takes a little bit to set up...

Rich N · 05/15/24

@isabonita If you know how to set up conditional dropdowns that would be amazing if you could show me.

Protonspounge · 05/17/24