An easy way to make the content of one combobox in Excel dependent on that of another

Comboboxes, aka dropdown boxes, are useful tools for constructing e-forms, restricting idiot-user responses to limited-choice fields and soon. Microsoft Excel has many a way of allowing you to create these, whether this be via the Forms toolbar, Control Toolbox toolbar or the Data -> Validation menu option.

One especially useful feature of such choice-enabling controls is that of making the choices in one box dependent on what the user chose in another. For instance, if a user chose "animals" in box 1, the other could allow "fox", "badger" and "pig" as options, but if they chose "vegetables" in box 1, the other box could only allow "carrots", "cauliflowers" and "cabbages" as choices.

There is a really quite easy shortcut to doing this in Excel, using a combination of the data validation features and the not-exactly-famous INDIRECT() function which changes a textual string into an Excel cell range reference. True, you could implement a more flexible and perhaps portable solution via VBA (check the combobox button in the Controls Toolbox, using perhaps some combination of its additem method or the listfillname property, and its change event) but this can be massive overkill for simple applications not to mention a bit fiddly to make the boxes line up nicely if you are more interested in a cell-based layout.

First you need to set up the various list options as named ranges in Excel - so start by using an otherwise unused part of a worksheet to write down the various lists. This includes the main list for the main combo box and, separately, each of the lists you want to appear in the dependent boxes.

To make named ranges you can either use the Insert -> Name -> Define menu commands, or type your desired name into the Name box which you can see to the left of the main formula bar. It normally displays the cell reference e.g. "A3" but select your list and overtype this with an appropriate name, hit enter, and it shall be henceforth named as you, the master, demanded.

So, make a named range for the master box 1 list (say "MAIN"), and also ones for the various selections you want to be presented to the user in the dependent boxes (say "OPTION1" and "OPTION2"). The key to this working is to ensure that you name the ranges you want to use as dependent lists with the same name as the option in the master list you want them to appear in conjunction with. This is very important in order for the INDIRECT() function to work.

When you're done with this, highlight the cell you want the main combobox to appear in, and go to the Data menu then Validation. Under Settings here, choose "Allow: List" and the type an equals sign and the name of the master box 1 list range (e.g. "=MAIN") into the Source box. Hit OK and this gives you your main combo box.

Now to make the dependent box, repeat the above steps in another cell, but this time let the source be an equals, the INDIRECT function and the direct cell reference to the main combobox cell - e.g. "=INDIRECT(A1)", if the master combobox is in cell A1. Press OK, and you're done.

Whatever text you choose in the primary textbox makes the secondary one change its source to that of the named range, as the INDIRECT function takes that text and converts it into the contents of the named range you set up earlier.

Because, upon re-reading, hardly any of that distracted explanation makes sense, here's a handy Excel demonstration file to see what the Poorhouse has been gibbering on about all this time.

(Technique stolen from a spreadsheet floating around work, ta.)


AttachmentSize
Dependent Excel comboboxes example14 KB

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <blockquote> <del>
  • Lines and paragraphs break automatically.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You may use [acidfree:xx] tags to display acidfree videos or images inline.
  • Images can be added to this post.

More information about formatting options

Captcha
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
3 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.