Combo Box - Forms

[lefty78312]

I have what should be an easy one, but I can't make it work. I'm displaying a list of items on sheet1, $a$2 (the actual info is on sheet2), and vlookup to lookup info about the selected item. It works perfectly until I add a combo box so users will know to click on the down arrow. The cell where the user makes their selection is linked to sheet1, $a$2, and the list from the input range shows perfectly, but columns 2 - 4 won't read the info in cell A2 when I use the combo box, and I get #N/A for an answer in those columns. I must be missing an important step.

The formulae in sheet1, columns B2:D2 are <code>=VLOOKUP($A$2,Sheet2!$A$2:$D$16,2,0), =VLOOKUP($A$2,Sheet2!$A$2:$D$16,3,0)</code>, etc.

Any idea where I'm going wrong?

 

[mikerickson]

If your combobox is from the Forms menu (not an ActiveX combobox) then the value in A2 is the index of the selection, not the text of the selection.

The formula in B2 could be
=INDEX(Sheet2!$A$2:$D$16, $A$2, 2)

Or you could use VLOOKUP formulas with an ActiveX combobox, which puts the list's value in the linked cell.

 

[lefty78312]

If your combobox is from the Forms menu (not an ActiveX combobox) then the value in A2 is the index of the selection, not the text of the selection.

The formula in B2 could be
=INDEX(Sheet2!$A$2:$D$16, $A$2, 2)

Or you could use VLOOKUP formulas with an ActiveX combobox, which puts the list's value in the linked cell.

I don't know how to use vlookup with an ActiveX combobox, but the index formula works perfectly. Thank you.