Question: I'm trying to create pull down boxes in Excel 2003/XP/2000/97 with selections to highlight and fill in the cell. How do I do that?
Answer: To create a combo box in Excel, the first thing that you need to do is display the Forms toolbar. To do this, under the View menu, select Toolbars > Forms.
The Forms toolbar should now appear. Select the combo box icon. Then left-click on the spreadsheet where you'd like the combo box object to appear and drag the mouse pointer to the right until the combo box is the desired size.
Next, you will need to enter somewhere on your spreadsheet, the values that you'd like to appear as selections in the combo box. We've entered our values in cells A2 through A4.
Next, right-click on the combo box and select Format Control from the popup menu.
When the Format Object window appears, select the Control tab. Then select the input range for the combo box. This is the cells that contain the values that you wish to see in the combo box.
Then select the Cell link. This the cell that is linked to the combo box. This cell will display the combo box selection.
Click on the OK button.
Now when you return to the spreadsheet, your combo box should display a selection of values. In our example, it displays Active, Inactive, and Unknown.
When you select a value, the linked cell should display the index of the selection. In this case, we selected "Inactive", so cell C5 displays a 2. The value of 2 represents the 2nd value in the combo box.
If you need to translate C5 back to the combo box selection, you can use the following formula: (but do not place this formula in cell C5, place it in a cell that you are currently not using)
=INDEX(A2:A4, C5)
The INDEX formula would return the value "Inactive".
