This is the Online Help page for the dynamic dropdown menu widget in WrapCreator.
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 ordinary 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. It also supports the rare case when a very long list of options is provided as a delimited list.
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, 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.
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.
By default, a dropdown menu allows the user to select one item only. When they do, the menu collapses (closes) to show only the selected item. If the user clicks on the menu again, it opens to allow them to select a different item, automatically unselecting the previous one to ensure that only one item is selected.
If you want the user to be able to select more than one item in a list, select Enable Multiple Selections. Multiple selections are useful if the user needs to select “family members that are adults”, “two or more cities”, or “up to five reasons”.
When multiple selections have been enabled, the dropdown menu does not collapse when the user selects an item. Instead, a checkbox appears to the right of the selected item and the menu remains expanded. Any number of items may be selected. If the user clicks on an item that has already been selected, it is unselected. To collapse the widget, the user needs to click outside the list of options.
The widget’s cell returns a text string containing all the selections delimited by semicolons. In the example above, the cell where the dynamic dropdown is placed would return
811110;813110;T900
An easy way to decompose such a string is to use the TextSplit function.
There is an example of a one-dimensional Dynamic Dropdown in the tutorial for the Link Image widget
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.
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.
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:
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.
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 (Index is in the) First Column setting. If the index is longer than the longest list of choices, this may be the 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 (Index is in 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 “major cities” example above, the Lookup Cell is the one-dimensional dropdown used to set the country. It can contain the value Japan. When the Lookup Cell matches a key in the index, the dynamic dropdown menu is populated with all the menu choices for that key. For Japan, those choices are Tokyo, Osaka and Nagoya.
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.
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.
Why stop at only two dimensions?`You can use one menu for a 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.
By default, a dropdown menu allows the user to select one item only. When they do, the menu collapses (closes) to show only the selected item. If the user clicks on the menu again, it opens to allow them to select a different item, automatically unselecting the previous one to ensure that only one item is selected.
If you want the user to be able to select more than one item in a list, select Enable Multiple Selections. Multiple selections are useful if the user needs to select “family members that are adults”, “two or more cities”, or “up to five reasons”.
When multiple selections have been enabled, the dropdown menu does not collapse when the user selects an item. Instead, a checkbox appears to the right of the selected item and the menu remains expanded. Any number of items may be selected. If the user clicks on an item that has already been selected, it is unselected. To collapse the widget, the user needs to click outside the list of options.
The widget’s cell returns a text string containing all the selections delimited by semicolons. In the example above, the cell where the dynamic dropdown is placed would return
811110;813110;T900
An easy way to decompose such a string is to use the TextSplit function.
If you have a very long list of options provided to you as a delimited list, it may be easier to use the delimited list directly in the dropdown from a single cell, instead of pasting each option into a separate cell the way one would naturally work with Excel. You enable this mode by selecting Generate options from a delimited list.
The options in the list are delimited by the character you designate as the Delimiter. The default delimiter is the vertical bar or pipe symbol as shown in the examples. You should make sure that the delimiter character you choose doesn’t appear in the text for any of the options since it will break these options into two. You can use any character as the delimiter except space.
Example: You have access to all the three-letter ISO-3166-1 alpha-3 country codes in a delimited list and you want to use them in a Dynamic Dropdown widget.
ABW|AFG|AGO|AIA|ALA|ALB|AND|ARE|ARG|ARM|...
Paste the list into any cell in the spreadsheet, designate this cell as the Options Range, assign a cell name and you have created a country code selector.
With dropdowns you often want a prompt as the initial selection, e.g. Please select your country code in the example above. In this case, you place the prompt at the beginning of the list as in the example below. If you want to make country code selection mandatory, you can just verify that the dropdown is no longer returning the prompt before you allow the Wrap to be signed.
Please select your country code|ABW|AFG|AGO|AIA|ALA|ALB|AND|ARE|ARG|ARM|...
Options in the list are automatically truncated for leading and trailing blanks, e.g. if the list contains
...FLK | FRA | FRO...
the corresponding option in the dropdown will appear as just “FLK”, “FRA”, and “FRO”.
By default, a dropdown menu allows the user to select one item only. When they do, the menu collapses (closes) to show only the selected item. If the user clicks on the menu again, it opens to allow them to select a different item, automatically unselecting the previous one to ensure that only one item is selected.
If you want the user to be able to select more than one item in a list, select Enable Multiple Selections. Multiple selections are useful if the user needs to select “family members that are adults”, “two or more cities”, or “up to five reasons”.
When multiple selections have been enabled, the dropdown menu does not collapse when the user selects an item. Instead, a checkbox appears to the right of the selected item and the menu remains expanded. Any number of items may be selected. If the user clicks on an item that has already been selected, it is unselected. To collapse the widget, the user needs to click outside the list of options.
The widget’s cell returns a text string containing all the selections delimited by semicolons. In the example above, the cell where the dynamic dropdown is placed would return
811110;813110;T900
An easy way to decompose such a string is to use the TextSplit function.
If you have a very long list of options, it may be time-consuming to locate the correct choice in the list. If you enable the AutoComplete option, a text field appears that allows users to enter text that matches the intended choice. In the example below, the user has started typing Be which automatically shortens the list to only the choices that contain this character string.
If you have enabled Autocomplete you can also enable Custom User Input that allows the user to type in a missing choice, e.g. to enter a missing street or city name directly into the field without validation.
If you have enabled Autocomplete you can set the Dynamic Dropdown as Required so that the user cannot save the wrap without selecting a choice.
When you work with delimited lists, you may need to manage long, aggregated text strings efficiently. For this purpose, WrapCreator offers the powerful TextSplit and TextGlue functions.
