Paired value dropdown list

[artz]

Hi,

I have a worksheet where a dropdown list would be a good idea. In columns O and P are paired stock tickers starting at row 2. I would like to create a dropdown list which would list together the values in O and P, O2 with P2, etc. When a pair is selected, the ticker symbols will be placed in cells B1 and C1, respectively.

Does anyone in the Forum have a suggestion on how to do this?

Thanks,

Art

 

[Smitty]

I'd create a lookup table with your pairings in column A, then each ticker in columns B & C respectively. Use column A for your validation list, then lookup against that for B & C. If you create it as a table, then you can name column A and use that for your list range, then it will be dynamic.

HTH,

 

[artz]

Hi Smitty,

Thanks for your response and suggestions. I've never used a Lookup Table and examples that I found online were confusing. I didn't see an example of what I need. Also, don't quite follow the rest of your suggestion.

Art

 

[artz]

All,

I changed course a little and found a solution. I decided that a listbox would work just fine for my application. The code below places the values found in the range O2:P50. When the listbox is clicked, the out goes to the target cells, B1 and C1:

Private Sub ListBox1_Click()
Dim lbtarget As msforms.ListBox
    Dim rngSource As Range

    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Chart").Range("O2:P50")
    
    'Fill the listbox
    Set lbtarget = ListBox1
    With lbtarget
        'Determine number of columns
        .ColumnCount = 2
        'Set column widths
        .ColumnWidths = "40;20"
        'Insert the range of data supplied
        Worksheets("Chart").Range("B1").Value = ListBox1.Column(0)
        Worksheets("Chart").Range("C1").Value = ListBox1.Column(1)
    End With
End Sub

Thanks,

Art