Create a dynamic drop-down in Excel in 5 minutes!

AN UPDATED VERSION OF THIS POST HAS BEEN PUBLISHED HERE: http://easy-excel.com/?p=254

In a previous post i made a drop-down menu in 3 minutes. Actually, if you take a look at this Youtube-video, you’ll see that it only took 1 minute!

Now for something really cool: Make a drop-down menu that changes dependent on what you choose in another cell. It only takes five minutes!

1. Enter the data (2 minutes)

The main category should be in column A and the sub-categories in the other columns.
Important: The headline of each sub-category must be exactly the same as the name in the main category!

2. Name the ranges (1 minute)

Select all the entries in a column and use the shortcut Ctrl+Shift+F3 to create a name for the range. Repeat this with every column.

3. Make the drop-down menu for the main categories (1 minute)

Select a cell, e.g. I3, and choose Data Validation on the Data tab (or shortcut Alt+D+L). Choose List, put the cursor in the Source field and press F3 to open the Paste Name window. Choose Categories.

4. Make the drop-down menu for the sub-categories (1 minute)

Select a cell, e.g. J3, and choose Data Validation on the Data tab (or shortcut Alt+D+L). Choose List, put the cursor in the Source field and type this formula:
=INDIRECT(I3)

We have created a dynamic drop-down menu! Can you imagine any easier way to impress your colleagues?

Author: Audun Danielsen

About these ads

10 thoughts on “Create a dynamic drop-down in Excel in 5 minutes!

  1. Pingback: Yellow pop-up box when selecting cell in Excel « Excel is easy!

  2. Pingback: Quora

  3. Pingback: Quora

  4. Pingback: Quora

  5. No problem, but a lot of work! The secret is the tables, and you would have to make a lot of them! For example, create a table called New York with the boroughs Manhattan, Bronx, Brooklyn, Queens and Staten Island. Name it (Ctrl+Shift+F3) and use the formula =INDIRECT(J3) in the Data Validation menu. Then, if you choose New York in J3, you will be able choose between the five boroughs in the newly validated cell.

  6. An impressive share! I have just forwarded this onto a coworker who was doing
    a little research on this. And he in fact ordered me lunch simply because I found it for him…
    lol. So allow me to reword this…. Thank YOU for the meal!!
    But yeah, thanks for spending the time to discuss this subject here on your web page.

  7. When I use the =INDIRECT(I3) I get an error message – The source currently evaluates to an error. What can be wrong?

  8. Pingback: Data Validation Formula

  9. So it seems to work perfectly fine – but only when my category names are single words. So for instance, “electronics” leads me to the correct drop down list, but when it’s “Food and Beverage” (3 words), excel doesn’t seem to find my source anymore :/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s