Thread: ComboBox Question

I have a spreadsheet with several ComboBoxes on it. Each combo box
links to the one lookup list, which contains text in the first column,
and a numeric in the second. I've hidden the second column from view
within the combo.

As an example, my first ComboBox has the following values set:

BoundColumn = 2
ColumnCount = 1
LinkedCell = D5
ListFillRange = Lookup!A3:B65536

The idea is as follows - The user selects a text item from the ComboBox
list, which is in Column A of the ListFillRange. The numeric value
that is in Column B of the ListFillRange--hidden from view in the
ComboBox--should be placed in the cell D5. So far, so good.
Everything works fine.

However, when I close the file and re-open it, the values that were
shown in the ComboBox have changed. What it would appear to be doing
is if, for example, the ComboBox contains the text "Grommit" and Cell
D5 then populates with 25, the number in stock. When I close the file
and reopen it, the ComboBox may have changed to "Dowel" with cell D5
still containing 25.

After investigating the data, it would appear as though the ComboBox is
updating itself with the text that is associated with the first entry
in the ListFillRange, Column B, which is the same as the value stored
in Cell D5, if that makes sense.

The ListFillRange is sorted alphabetically by Column A, and I can't
think of anything to do to correct the problem.

What I want is:
Select an item from the ComboBox
Display the text of the item in the ComboBox
Display the numeric value associated with the text in the cell next to
it
Keep the text and numeric in place when I save the file, close it
and re-open it.

Anyone any ideas?

Regards

Duncs

How about linking the combobox to the first column--does that first column have
a list of unique entries?

Then you could use:
=if(d5="","",vlookup(d5,lookup!a3:b65536,2,false))
to return the quantity.



Duncan Edment wrote:
>
> I have a spreadsheet with several ComboBoxes on it. Each combo box
> links to the one lookup list, which contains text in the first column,
> and a numeric in the second. I've hidden the second column from view
> within the combo.
>
> As an example, my first ComboBox has the following values set:
>
> BoundColumn = 2
> ColumnCount = 1
> LinkedCell = D5
> ListFillRange = Lookup!A3:B65536
>
> The idea is as follows - The user selects a text item from the ComboBox
> list, which is in Column A of the ListFillRange. The numeric value
> that is in Column B of the ListFillRange--hidden from view in the
> ComboBox--should be placed in the cell D5. So far, so good.
> Everything works fine.
>
> However, when I close the file and re-open it, the values that were
> shown in the ComboBox have changed. What it would appear to be doing
> is if, for example, the ComboBox contains the text "Grommit" and Cell
> D5 then populates with 25, the number in stock. When I close the file
> and reopen it, the ComboBox may have changed to "Dowel" with cell D5
> still containing 25.
>
> After investigating the data, it would appear as though the ComboBox is
> updating itself with the text that is associated with the first entry
> in the ListFillRange, Column B, which is the same as the value stored
> in Cell D5, if that makes sense.
>
> The ListFillRange is sorted alphabetically by Column A, and I can't
> think of anything to do to correct the problem.
>
> What I want is:
> Select an item from the ComboBox
> Display the text of the item in the ComboBox
> Display the numeric value associated with the text in the cell next to
> it
> Keep the text and numeric in place when I save the file, close it
> and re-open it.
>
> Anyone any ideas?
>
> Regards
>
> Duncs

--

Dave Peterson