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…
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.
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:
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.
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 🙂