Prev: Calculations
Next: Address of cell in VBA
From: KH on 5 Apr 2010 03:25
I have many items to input into my selection list in my worksheet. Hence, I
came up with the Combo Box (from FORM under Toolbars) function.

In addition, based on the "answer" selected from the combo box, I need to
generate the other details based on this "answer".

Is it possible to incorporate my Combo Box selection "answer" as my
lookup_value in my Vlookup function. If yes, how do i proceed?

Thank you!
From: Dave Peterson on 5 Apr 2010 08:16
You can use a couple of cells to get the value from the DropDown (from the Forms
toolbar).

If you rightclick on that dropdown, then choose Format Control, you can go to
the control tab and assign a linked cell (in an out of the way location--or even
a hidden worksheet).

But this linked cell returns an index into that list.

You can use a formula like this to return the value:

(say in B1)
=if(a1="","",index(sheet2!a:a,a1,0))

Where A1 is the linked cell and sheet2 column A contains the list for the
dropdown.

Then you can use this hidden cell in your =vlookup()

=if(b1="","",vlookup(b1,sheet99!a:e,5,false)

But depending on what you're returning and how you're using it in the =vlookup()
formula, you may need to use =indirect() (say you're returning the worksheet
name that contains the table for the =vlookup():

=if(b1="","",vlookup(x999,indirect("'"&b1&"'!a:e"),5,false)



KH wrote:
>
> I have many items to input into my selection list in my worksheet. Hence, I
> came up with the Combo Box (from FORM under Toolbars) function.
>
> In addition, based on the "answer" selected from the combo box, I need to
> generate the other details based on this "answer".
>
> Is it possible to incorporate my Combo Box selection "answer" as my
> lookup_value in my Vlookup function. If yes, how do i proceed?
>
> Thank you!

--

Dave Peterson
From: JLatham on 5 Apr 2010 08:24
With the combo box from the Forms toolbox you can 'link' it to a cell so that
when you pick a value in the combo box, that value is placed into the linked
cell. You could then reference that cell in your VLookup formula.

Right-click on the combo box and choose [Format Control] and then use the
[Control] tab to set the address of the linked cell. You can probably
double-click on the combo box and have it pull up the [Format Control] dialog
also, if you have the Forms toolbox displayed when you double-click on it.

"KH" wrote:

> I have many items to input into my selection list in my worksheet. Hence, I
> came up with the Combo Box (from FORM under Toolbars) function.
>
> In addition, based on the "answer" selected from the combo box, I need to
> generate the other details based on this "answer".
>
> Is it possible to incorporate my Combo Box selection "answer" as my
> lookup_value in my Vlookup function. If yes, how do i proceed?
>
> Thank you!
 | 
Pages: 1
Prev: Calculations
Next: Address of cell in VBA