Value from a control (Excel 2002)

Topic

New Reply

I want to create a vlookup formula based on the value in a combo box control. I can’t remember how to find the name of the control to reference it in the vlookup formula. When I select the combo box and goto Insert, Name, Define the name at the bottom of the dialog is “Drop Down 1033”.

I want the vlookup formula to look at the value (text) in the combo box, then goto a named range. The formula I tried is
=vlookup(“Drop Down 1033”,salesno,2,false)
I get the NA error.

Any ideas?

Thanks,
Craig.

Reply | Quote

Replies

hi Craig,

set the linkedcell property to for example to Sheet1!$A$1 and then use that cell in your vlookup function:

=vlookup(Sheet1!$A$1,salesno,2,false)

greetings,

Reply | Quote

Another way (since you are already doing a “lookup” using the pulldown, I assume the ListFillRange is SalesNo)

Set the BoundColumn Property to 0 (zero), Linked cell to Sheet1!$A$1
And use the formula:
=index(SalesNo,Sheet1!$A$1+1,2)

Note: the “+1” is because the control toolbox arrays are “zero-based”: the first item is 0, not 1.

VLOOKUPs are slower than INDEX and it will speed it up if you don’t use them a lot. Since you get the “row” directly from the combobox, there is no need to use VLOOKUP.

Steve

Reply | Quote