MS-Excel / Functions and Formula

---

Support Multiple Lists in a ComboBox

When working with multiple lists, you can force a list to change by using a combination of option buttons plus a ComboBox.

Excel offers many ways for users to select items from a list, be they names, products, days of the week-whatever the list is composed of. However, to access more than one list of choices simultaneously generally requires that you use three separate controls, such as three ComboBox controls from the Form Controls (pre-2007, the Forms toolbar).

Instead, you can use a ComboBox in combination with option buttons (also called radio buttons and also found on the Form Controls) to have a list change automatically according to which option button you choose. To see howthis works, enter the numbers 1 through 7 in the range A1:A7 on a new worksheet. In B1:B7, enter the days of the week starting with Monday and ending with Sunday. In C1:C7, enter the months January through July.

Excel's auto-fill features can make this task much easier. Enter 1 in cell A1, select cell A1, press the Ctrl key, and then left-click the fill handle. While holding down the left mouse button and the Ctrl key simultaneously, drag down to row 7. Excel will fill in the numbers for you. Next, enter Monday in cell B1 and double-click the fill handle for this cell. Enter January in cell C1 and double-click the fill handle for this cell. Excel will fill in the days and months for you!

Select Developer → Controls → Insert → Form Controls (pre-2007, View → Toolbars → Forms) and press Option. Then, click the spreadsheet anywhere in three separate spots to place three option buttons on the spreadsheet.

Now, follow the same process but this time click the ComboBox option under Form Controls and again click somewhere on the spreadsheet to insert a ComboBox on it. Using the drag handles, size the ComboBox to a manageable size and position the option buttons so that they're directly below the ComboBox.

Right-click the first option button, select Edit Text, then replace the words Option Button 1 with the word Numbers. Use the same process for Option Button 2, replacing it with the word Weekdays, and for Option Button 3, replacing it with the word Months.

While holding down the Ctrl key, click each option button so that all three are highlighted, then right-click and select Format Control → Control (on the Mac, you can select View → Toolbars → Drawing, and use the Select Objects tool to select the group). Specify cell $F$1 as the cell link (make sure it is absolute-use those dollar signs).

In cell E6, enter the following formula:

=ADDRESS(1,$F$1) & ":" & ADDRESS(7,$F$1)

Select Formulas → Defined Name → Define Name (pre-2007, Insert → Name → Define). Under Name (pre-2007, Names in Workbook), type MyRange and in the Refers To: field, type the following:

=INDIRECT($E$6)

Click OK. Right-click the ComboBox and select Format Control → Control. Make the Input range MyRange and the cell link $G$1, then click OK. You should be able to select one of the option buttons, and the list within the ComboBox should automatically reflect which option button you chose.

When setting this up for your own spreadsheet, you should use some offscreen cells for the ComboBox links and lists. You might even want to hide these cells from users so that your links stay where they should. Also, you need to modify the two ADDRESS functions to reflect the cell range you are using. In the ADDRESS functions we used in this example, 1 represents the first row number of the lists, while 7 represents the last row number.