Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value from a control (Excel 2002)

    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.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value from a control (Excel 2002)

    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,

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Value from a control (Excel 2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •