Dynamic dropdown menu

This is the Online Help page for the dynamic dropdown menu widget in SpreadsheetConverter.

The purpose of the Dynamic dropdown widget is to make it easy to create a dropdown menu where the options are controlled from the spreadsheet and can be changed by a formula. If you only need a simple, static list of options, it is usually easier to use the Dropdown list widget in the Basic widgets category.

Due to the inherent complexity of the Dynamic dropdown widget, it cannot be tested in Excel. You must convert your spreadsheet to a web page to test a Dynamic dropdown menu.

The Dynamic dropdown widget operates in one of two modes, either with a one-dimensional table or a two-dimensional indexed table with lookup.

Screenshot of the mode selector in the dynamic dropdown widget

One-dimensional mode

In the one-dimensional mode, you provide a list of options in a cell range in your spreadsheet. The cell range can be either row- or column-oriented, and empty cells are ignored. The widget illustrates this, as you can see above, with a cell range containing three choices in a row.

In contrast to the basic dropdown widget, however, this cell range need not contain a static list of choices.

  • You can ask a customer to select one of the credit cards that the customer has on file.
  • In an order form, you can have a “Quantity” menu for each product contain a dynamically calculated list of accepted ordering quantities, i.e. the number of items in a box, carton or pallet.
  • You can create a list of only those account numbers that are eligible for a certain transaction, e.g. checkings or savings accounts.
  • You can provide a list of destinations that only include those destinations that fulfill a set of criteria defined elsewhere in the spreadsheet.

Each cell can contain a different formula that assembles, calculates or conditionally selects content for the menu item. Here are a few examples of formulas you can expect to find in a dynamic dropdown:

  • =if(country_name="", city_name, city_name & ", " & country_name)
  • =vlookup(postcode, postcode_to_city_table, 2, FALSE)
  • =if(location_1_available, location_1_name, "")

Make the cell range big enough to accommodate the largest number of choices you will ever need. If you don’t need all choices there’s room for, just leave the redundant cells empty and they will be ignored when the dynamic dropdown is created.

If you want to add a prompt, e.g. Select one of your credit cards on file at the top of the menu, just make it the first choice in the cell range.

Screenshot of a spreadsheet with options for a dynamic dropdown menu

Two-dimensional mode

The two-dimensional mode is best explained with an example. Let’s say we want to show the world’s biggest cities per country. We create a list of countries in a column, and a list of cities in the same row as the corresponding country.

In the table below, the index is located in the first column, in cells A2:A11. For each country, there is a list of cities on the same row. For Japan, the biggest cities are Tokyo, Osaka and Nagoya, in that order.

Screenshot of a table in Excel with countries and cities

It is very easy to display this index as a country selector, using a one-dimensional dynamic dropdown. Note that we have already placed a user prompt in cell A1 at the top of the list. Just select A1:A11 in the worksheet above as the cell range to provide the choices for the one-dimensional dynamic dropdown.

When the web page is shown in the web browser, the dropdown menu will display the user prompt Select country. In the example, the user has selected India.

Screenshot of a dynamic dropdown menu with countries taken from a table

When the user moves on to the second dropdown, its contents are set dynamically, depending on the country selection. For India, the choices are located in cells B5:G5 of the spreadsheet above, and the city dropdown would immediately change to look like this:

Screenshot of a list of cities for the selected country

Every time you change the country in the upper dropdown menu, the appropriate cities appear in the lower dropdown menu. This can dramatically increase the speed of data entry, the accuracy and the quality of an electronic form.

Settings for the two-dimensional mode

In the two-dimensional mode, you provide the cell range for an indexed, two-dimensional table in the Options Range setting.

Do you want the index in a column, with the options in the cells to the right, as in the country/city example above? We call this the First Column setting. If the index is longer than the longest list of choices, this may be easiest layout to work with.

If the index is short but the number of options can be long, it may be more convenient to use the First Row setting. In this setting, the top row of the Options Range contains the index, with the menu choices for each key in the cells below it.

A separate Lookup Cell is matched against the index. In the example above, the Lookup Cell is the one-dimensional dropdown used to set the country. In the example above, it can contain the value Japan. When the Lookup Cell matches a key in the index, the dynamic dropdown menu is populated with the menu choices for that key. For Japan, those choices are Tokyo, Osaka and Nagoya.

Screenshot of the options range and lookup cell options for a dynamic dropdown menu

Dynamic Lookup Cell

You can use many types of widgets for the Lookup Cell, e.g. radio buttons, sliders and even check boxes.

The value for the Lookup Cell doesn’t have to come from a widget, any cell in the spreadsheet can be used for lookup.

  • You have a table of sales tax (VAT)  per country, and you already know the country from the ship-to address.
  • You have a survey where questions are responded to with either Yes/No or the values 1-10. Instead of defining all the dropdowns manually you use the same options range for all questions in the survey, and use a fixed setting per questions to select the response type. If you later want to change the options to True/False or 1-5, you only have to change the options range.

Dynamic menu contents

Dynamic dropdowns take their choices from a cell range in the spreadsheet. You may fill this cell range with any kind of content and change it depending on other cells.

As an example, you may know the origin, destination, dimension and weight for a pending shipment. Using this information your spreadsheet dynamically assembles a price list from three couriers. All the user has to do is to determine the urgency and pick the best choice.

Screenshot of a dynamic dropdown with dynamically assembled content

Download the sample spreadsheet

See the conversion of the sample spreadsheet

Advanced applications

Why stop at only two dimensions?`You can use one menu for country, a second for postal codes per country, and a third for cities per postal code. And a fourth for streets per city! There is no technical limit to the number of levels or “dimensions” in such a menu structure.

You can create all the menu choices in the Options Range using the same programming techniques as we described for the one-dimensional dropdown above.

The first time you open a web page with a dynamic dropdown, the default choice will be the first in the list. If you later open the same form or calculator again, the value you previously selected will be pre-selected automatically.