Combo Box Anomaly

[Garyw]

I've added a combo box to a worksheet with the Input range from
another worksheet Example: GeneralLookups!$B$4:$B$13
Contained in the cells B4:B13 are values 200,300,400/500, etc.
When I select one of the values in the Combo Box it returns line
numbers 1 through 5 instead of the value in the cell.
It seems that it would have to go out of it's way to come up with this.
What's causing this?

Garyw

 

[Chip Pearson]

That's the way Forms combo boxes work. The result placed in the CellLink
cell is the 1-based index into the list of data values. Thus, if you select
the 3rd item in the combo box, the result is 3, regardless of what the data
values might be. You can use the OFFSET function into the data list to get
the actual value. For example, if a combo box has a CellLink to cell D5 and
pulls its data from the range starting in B11,

=OFFSET($B$11,D5-1,0,1,1)

returns the item selected in the combo box.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

Just to add to Chip's response.

If you used the combobox from the Control Toolbox toolbar, then you'll get the
value of the selected item--not the index number.

The dropdowns from the Forms toolbar will give you that index number in the
linked cell.