Create dependant drop downs

Using data validation in Excel, it is possible to create a drop-down selection into your web application. Appizy now manages dynamic drop-down, which means the options are updated every calculation cycle. This is very handy to create cascading (dependant) selectors in your web-app.

Let’s dive into how this works with an example: a delivery cost calculator. This example is available among the samples of the converter, as a starting for your own tools. We invite you to download the spreadsheet to follow the explanations!

In this widget price and delivery delays are relative to the country. To make it easier for the end user, we ask our visitor to choose first the continent and then a country in this continent.

Our calculator has 3 tabs: the first one is the widget itself. It should be the only one visible in a production ready application. The “Countries” tab contains all countries grouped by continent. It is used to create the options in the second second. Finally, the third tab contain the delivery cost associated with each country.

The first dropdown in based on the cell range of available continents. For the second one, the aim is to create a range of cells containing the list of country in a given continent. We do this using the OFFSET function.

The OFFSET function syntax has the following arguments:

=OFFSET(Reference, Rows, Cols, Height, Width)

Reference: the reference from which you want to base the offset. In our case the top-left cell of the countries sorted by continent (“Finland”).

Rows: the number of rows that you want the upper-left cell to refer to. Always 0 in our formula.

Cols: the number of columns, that you want the upper-left cell of the result to refer to. This is the match of the continent in the horizontal list of continent.

Height: the height, in number of rows, that you want the returned reference to be. Each continent has a variable number of countries. We have calculated the size of each list and the second tab. We retrieve it using INDEX.

Width: the width, in number of columns, that you want the returned reference to be. Always 1.