Cascading Combo Boxes in Excel


Cascading Combo Boxes in Excel aka Depending Lists
Everyone who uses Access knows about the Cascading combo boxes they can create. What does this mean you might ask. Well suppose you have a combobox with several options to choose from. The next combobox also has options to choose from. Often you would want to see only the options that apply to the option chosen in the first combobox. In other words the second option you can choose depends on the first option you have chosen. Tip: click on the images to enlarge…

Combo_I AccesCombo_II_Access

 
Combo_III_Access

 

 

 

 

 

 

Now how is this done in Excel?
Well actually there are various scenarios here to choose from but in this first post I will show the the easiest one. In a next post I will show you how you can use this in your forms created from VBA.

Goal to accomplish
What we are trying to accomplish is the following scenario. We have a cell where we choose a  category option. In this case we would like to choose from the following options: Excel, Word and PowerPoint. Depending on the choice we have made we would like to choose from the following list the available features for that specific application. Ok ready to do this? Here we go.

Create a sheet containing the data as shown below

 

 

Now we have to ‘name’ the various sections. Let start with the category section first. Name the first selection [Category]. You do so by selecting  “E3:E5” and then click in the Name Box and type Category. Mind you that no spaces are allowed in the name you are going to use. Furthermore you have to press the [Enter] key to add it to the names.

 

 

Now repeat the same steps for the Excel, Word and PowerPoint columns. So for Excel select “F3:F8” and name the selection [Excel]. For Word select “G3:G8” and name this selection [Word] in the Name Box, finally for PowerPoint select “H3:H8” and name this [PowerPoint] in the Name Box
Now place the cursor in cel “B2”. This will be the cell where we are going to create a ‘dropdown box’ to choose from which contains the categories.
Now go the the “Data”-tab in the Ribbon and look for “Data Validation”. Choose the first option and set the following options:

 

 

 

 

 

 

 

 

 

 

 

 

 

When done you will see a little arrow appear next to the cell. Click the arrow and your dropdown box should now show the three options to choose from.

 

 

 

 

Ok, now we create a ‘dropdown’ box in cell “B4” where the various features show up which depend on the choices made in the categroy list. So if Excel has been chosen we should only see the options belonging to the list under “Excel”. To do so place the cursor in cell “B4”
Again visit the [Data Validation] button in the Ribbon and choose the first option. Now set the following options in the dialog you see as this:
For an explanation about the INDIRECT function i refer to the site of Chip Pearson who has a clear explanation about this:
http://www.cpearson.com/excel/indirect.htm

When clicking OK you will be notified that “the source evaluates in an error”. Click continue because that is a logical error because nothing has been chosen yet from the first combobox “Category”.

The end result: ok, let’s try this. First select a category from the category list and then select an option from the features list. You will see that if you change the category that the features list will change to the options belonging to the categroy chosen.

Cool right?

In a next blogpost I will show you how you can use this in a form in VBA and use the lists createdon a sheet. I will also show you how to use more combo’s being depended on others.

Be cool, be Excellent 🙂

Maus

Advertisements

5 thoughts on “Cascading Combo Boxes in Excel

  1. Bill W

    How do you handle it when your “Level 1” combo box item has a space in its name.
    Excel will not let you name a range with a space in it.
    For example, I have a level one item called “Change Notice”
    Then, I have another group of items related to the Change Notice, but when I go to name that group “Change Notice” it gives me an error. Will excel equate Change Notice to Change_Notice

    Thanks!

    Reply
    1. Maurice Post author

      Bill,
      Make sure the name you use doesn’t have any spaces in them. I use the underscore to create a visible space but in fact it isn’t there. So if you use [Group Notice] i will name that section [Group_Notice] and then you can refer to it. Let me know if that makes it clear for you otherwise i’ll try to explain with the examples i’ve made.

      Maurice

      Reply
  2. Pingback: Building a drop down spreadsheet in excel

  3. Robert Ian Shepard

    Excellent tutorial! I just used this article to learn how to make these boxes for work, you demonstrated it very clearly.

    Reply

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