See this screencap:
http://img126.imageshack.us/img126/4921/cboxkh8.jpg
A combo box from the Forms toolbar is different from a data validation drop
down list in that a data validation drop down list places the selection you
make into the cell that holds the drop down. A combo box doesn't do that. A
combo box will place the *number* of the item selected in a *linked cell*.
Then, if you need to refer to the selection made you need a formula that
will return the item selected based on the item number of the linked cell.
In the screencap I've drawn the combo box on top of cells D2 and E2. It's
important to know that a combo box is an *object* and an object does not
reside in a cell. An object "sits" on top of the worksheet. This means we
can use cells D2 and E2 and because our combo box sits on top of those cells
whatever we put in cells D2 and E2 will be hidden from view.
So, we're going to use cell D2 as the linked cell and we'll use cell E2 for
the formula that will return the item selected from the list.
Assume you've drawn a combo box over cells D2 and E2 as in the screencap.
Select the combo box and right click. A menu will appear:
http://img459.imageshack.us/img459/7926/cbox1tv3.jpg
Select Format Control>Control tab
http://img514.imageshack.us/img514/3459/cbox2qs8.jpg
The Input range is the source of the list - $A$1:$A$10
The linked cell is $D$2
When you make a selection from the list the item *number* is returned to the
linked cell. In the screencap the selection is "green" and it's the 3rd item
in the list. The value returned to the linked cell is 3. To return the
actual item we need a formula. I've put that formula in cell E2:
=INDEX(A1:A10,D2)
The formula in E2 returns green. So, to refer to the item selected from the
combo box you need to refer to cell E2:
=E2
Returns: green
