Dependent Combo Box in Excel

You can add a drop down list in a worksheet cell, so people can select from a list of valid entries. The font in that list is pretty small though, even if the worksheet is at 100% zoom. And good luck trying to read it, if you go to a lower zoom setting! My eyes aren’t that good, so I use a pop-up combo box, in some workbooks, to make the list easier to use.

Combo Box Advantages

On the worksheet, there’s only one combo box, and it pops up when you click or double-click a cell that has a data validation drop down list.

There are a few benefits to using combo boxes:

• You can change the font size, so the text is big enough to read
• You can change the number of visible items, to reduce the amount of scrolling that’s needed
• When you start typing, the matching item is filled in (autocomplete)

Combo Box Disadvantages

Of course, there are some disadvantages too, so keep those in mind if you decide to use a combo box popup

• This technique uses programming, and wipes out Excel’s Undo list, like most macros do
• The single-click option for the code runs every time you select a different cell on the worksheet, so that could slow down a large workbook
• The combo boxes are ActiveX controls, so they won’t work in Excel for the Mac

Dependent Combo Boxes

The previous sample files that I’ve uploaded were designed to work with a simple list of items on the same sheet, or a list in a named range on any sheet. They didn’t work with a dependent list though, such as a list of cities based on the region selected in a different cell.

In the screen shot below, column C has data validation that uses a simple INDIRECT formula, =INDIRECT(B3),

If you tried the combo box with that type of data validation list, it just showed an empty list. So sad!

New and Improved Dependent Combo Box

To prevent the heartbreak of empty combo box lists, I finally uploaded a new combo box sample file. It doesn’t handle fancy formulas, but at least is shows a drop down list if a cell has a simple INDIRECT formula.

Download the Dependent Combo Box File

You can learn more about the Dependent Combo Box on my website, and download the sample file.

____________________