top of page

Excel Dynamic Selection List (dropdown menu)

In this episode we discuss how to create a dropdown menu in a very simple way.

Tip: Watch the video and practice with me in the Excel file

The work file can be found HERE

As an example, we use a tab that contains address information.

We are going to create a selection list or dropdown menu in another tab that will work dynamically.

​

What is the difference between a static and dynamic dropdown menu?

A static dropdown menu displays some selected data.

But will no longer show this for new entries if it is out of range.

For example, if you select in column B from row 4 to row 41, this dropdown menu will display 37 selections.

But if we are going to make a new entry in the addresses on row 42, the static dropdown menu will not show the data from row 42.

With a dynamic this will be shown.

To create a dynamic dropdown menu we need 2 important formulas.

These are the offset and number arg formulas.

These formulas are known in English as the offset(shift) and counta(number of arg).


What do we need to know to understand these 2 formulas :

  1. The value to which cell or starting point we want to refer. We also make this value absolute with the $ signs so that this value cannot change in the column as well as in the row. In our example this is = shift(address list!$B$3;

  2. In this value we are going to determine how many rows we will go down and be visible in the application where we are going to use the formula. In the example we go down 1 row and this becomes the value, which means that we are looking at B4. =shift(address list!$B$3;1;

  3. In this value we are going to determine how many columns we are going to move. We want to stay in columns B, so our value becomes 0. =shift(list of addresses!$B$3;1;0;

  4. The next step is to determine the height and with this we will soon create the magic of the dynamic by using the number arg formula. To practice, you can enter different numbers here. For example, if we want to display 40 cells, we would enter 40 as a value. =shift(address list!$B$3;1;0;40;

​

What should we do now to make this dynamic?


For this we will use the =numberarg formula.

This formula counts how many nonblank cells there are in the range.

Don't know this Function yet?

Watch the video HERE

For example, if we type =numberarg($C$4:$C500) you will see in this example that you will get 42.

This is because there are 42 cells that are not empty.


Tip : It is always recommended that your end range is large enough and that you make clear marking and communication so that the user always knows where the range of the dynamic formula stops.

In our exercise you will see that we work with a table layout.

This sometimes has some advantages.

If we select from the beginning to the end of the table, you will see that the number arg formula will adjust as the row gets longer or shorter.

​

Tip: Use Ctrl+shift+down arrow to select your entered range from top to bottom.

In our exercise, we will place the =numberarg formula in cell H3 of our dynamic tab.

We are now going to select any cell to bring our shift and number arg formula together into our dynamic formula.

So this becomes =shift(address list!$B$3;1;0;$H$3;1)

As you can see there is a 1 after $H$3$. This value represents the number of columns to use. How are we going to process all this in a dynamic dropdown menu?

There are 2 ways to do this:

  1. copy the entire formula .

  2. Via the ribbon, go to DataData ValidationData ValidationSettings List and paste the complete formula into the source and press OK or enter.

Tip: Use keyboard shortcuts âž¡ Alt âž¡e1âž¡hâž¡v

  • If everything goes well, your dynamic dropdown menu will now work! Copy the complete formula.

  • From the ribbon, go to FormulasManage NamesNew...type any name. At the Name box and paste the formula at : Refers to: We use Drop_name in this example

  • From the ribbon, go to DataData ValidationData ValidationSettings List and type or paste whatever name you chose.

If all goes well, your Dynamic dropdown menu will now work!


Good luck and keep practicing.

​

Did you find this video interesting?

Would you like to make a small donation?

Thanks in advance.

​

bottom of page