Create A Variable Drop Down List In Excel

Hello Excellers and welcome to another #formulafriday blog post. Today let’s look at how to create a variable drop-down list in Excel. That’s right to be able to choose a value from one cell, and the options then available in your list change depending on the value you have chosen. For example, I have a drop-down selection of Jeans (various styles) which are only available in specific sizes depending on the type of style.

So, my Jeans that are Skinny are only available in sizes 8,10,12 and 14. So when the user chooses this product we only want the available sizes to be displayed for selection for order. This process needs to be available for all Jean styles.

The Solution.

We can solve this problem by using DATA VALIDATION and the OFFSET function.

STEP 1. Set Up Named Ranges. The first step is to set up some named ranges. These form the drop-down options for our Jean Style selection area. If you want to read more about named ranges you can check out my blog post below.

Formula Friday – 3 Reasons To Use Named Ranges In Your Excel Formula

In this example, I have named the range B3:F3 skinny, B4:F4 super_skinny, B5:F5 regular_cut and B6:F6 boyfriend. You can see my first named range below in the screenshot, “skinny”.

STEP 2. Create a drop-down list to allow the user to select the Jean Style. Do this by using a DATA VALIDATION list.

Data Tab | Data Tools Group | Data Validation

Settings Tab | Under Validation criteria allow List

In the source dialogue box select the list of cells that contains the values that you want in your list. In this example, it is the contents of the cells A3:A6.

STEP 3. Now we need to set up our Sizes Available drop-down list which we need to associate with the Jeans Style. We do this by using the INDIRECT Function. This is one handy little function. If you have not come across or used this before then here is a quick summary of its syntax.

INDIRECT SYNTAX

=INDIRECT (ref_text, [a1])

where

ref_text is a reference supplied as text. This is a required argument.

a1- this is an optional argument

INDIRECT returns a valid reference from a given text string. Or in simpler terms, the INDIRECT function allows you to put the address of one cell in another, and get data from the first cell by referencing the second. For example, if cell A1 has the value “E4”, then = INDIRECT will return the value in E4. In our example, the INDIRECT function looks for the area of your worksheet which has the same name as the named range you created in Step 1.

Data Tab | Data Tools Group | Data Validation

Settings Tab | Under Validation criteria allow List

In the source dialogue box select the list and type the following formula =INDIRECT $H3

I have set up my columns H and I respectively to demonstrate the two columns of cells used the create the Jeans Style orders column and the Associated Sizes with those Jean Style.