Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Problem with drop down and Vlookup (Might use a while loop)

    With the embedded worksheet, I am doing a dropdown where I want the output to have multiple values. Right now, after I put in the secondary value, the output cell shows "#NA". I've been thinking of doing a while loop for the dropdown. I'm a VBA novice, so I would like to stay away from it unless it is explained well.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Welcome to the Lounge!
    The dropdown isn't designed for multiple selection. That is the wrong tool for the job.
    We can use other multi-select options that may be more appropriate.
    If you can explain a little more on what you actually want to do, we could give a better solution.

    zeddy

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks and I will Zeddy, let me explain what I want to happen

    2015-05-20_14-43-25.jpg

    The example is to automatically extract values using a dropdown. The Dropdown (B2-B5) has a selectable 1-4 value. This 1-4 value is referenced in Column E. The Column E value has a conjunctive letter attached to the numerical value (F2-F5). The output of the Column F value is sent over to Column C for the user to validate.

    For example if the user selects "2" in B2 dropdown. That B2 value is referenced in E3 (which is 2), that E3 is a joint refenece to the F3 value of "B". That B value is sent back to C2 via Vlookup.

    Like in the first post, the multiple selection value is bringing in an "#NA" value. Any help alleviating this is well appreciated

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Who's on First?

    A sample file would be MOST useful.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    RetiredGeek, the sample is in the original post, but here it is again..

    sampleBook.xlsm

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Eddie,

    Sorry for missing the link in #1. I'm still a little lost as to just what the purpose of all this back and forth referencing is? It would seem to me that a simple formula in C like: =Char(B2+64) would do the trick. Plus the fact that you can't get 2 values in col B with a dropdown. So what am I missing? HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Perhaps this:
    Code:
    Private Sub WorkSheet_Change(ByVal Target As Range)
    
        Dim oldval                As String
        Dim newval                As String
        Dim Values()              As String
        Dim sOut                  As String
        Dim n                     As Long
        Dim vMatch
        Dim rData                 As Excel.Range
    
        If Target.Count > 1 Then Exit Sub
        If Target.Column = 2 Then
    
            Set rData = Range("E2:F5")
    
            newval = Target.Value
            If newval <> "" Then
                Application.EnableEvents = False
                Application.Undo
                oldval = Target.Value
                If oldval = vbNullString Then
                    Target.Value = newval
                Else
                    If InStr(oldval, newval) = 0 Then Target.Value = oldval & "," & newval
                End If
                Values = Split(Target.Value, ",")
                For n = LBound(Values) To UBound(Values)
                    vMatch = Application.Match(CLng(Values(n)), rData.Columns(1), 0)
                    If Not IsError(vMatch) Then sOut = sOut & "," & rData.Cells(vMatch, 2)
                Next n
                Target.Offset(, 1).Value = Mid$(sOut, 2)
                Application.EnableEvents = True
            End If
        End If
    
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  8. The Following User Says Thank You to rory For This Useful Post:

    eddiepliers (2015-05-21)

  9. #8
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The thing is this. This is a sample of a much larger spreadsheet that will grab values and insert the looked up value into the "C" column when (in this example) items are searched in the B column. columns E and F are where the data is stored.

    In this exercise, it's not about back and forth, it's about gathering data from another place and putting it closely to where the original selection is.

  10. #9
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Rory! The code works! Thank you very much! Now I will try to bring it into the larger spreadsheet without any problems.

    Thank you!

  11. #10
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Perhaps this:
    Code:
    Private Sub WorkSheet_Change(ByVal Target As Range)
    
        Dim oldval                As String
        Dim newval                As String
        Dim Values()              As String
        Dim sOut                  As String
        Dim n                     As Long
        Dim vMatch
        Dim rData                 As Excel.Range
    
        If Target.Count > 1 Then Exit Sub
        If Target.Column = 2 Then
    
            Set rData = Range("E2:F5")
    
            newval = Target.Value
            If newval <> "" Then
                Application.EnableEvents = False
                Application.Undo
                oldval = Target.Value
                If oldval = vbNullString Then
                    Target.Value = newval
                Else
                    If InStr(oldval, newval) = 0 Then Target.Value = oldval & "," & newval
                End If
                Values = Split(Target.Value, ",")
                For n = LBound(Values) To UBound(Values)
                    vMatch = Application.Match(CLng(Values(n)), rData.Columns(1), 0)
                    If Not IsError(vMatch) Then sOut = sOut & "," & rData.Cells(vMatch, 2)
                Next n
                Target.Offset(, 1).Value = Mid$(sOut, 2)
                Application.EnableEvents = True
            End If
        End If
    
    End Sub
    Rory, I have one question. My data is coming from another tab in the worksheet. I tried Set rData = Range("'OtherTab'!E2:F5") and it's not bringing back the multiple values.

    Thanks in advance

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It would be:
    Code:
    Set rData = Sheets("Other tab").Range("E2:F5")
    Regards,
    Rory

    Microsoft MVP - Excel

  13. The Following User Says Thank You to rory For This Useful Post:

    eddiepliers (2015-05-21)

  14. #12
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Great. Thanks Rory.

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    What do you want to happen if you delete the contents of the dropdown cell?

    zeddy

  16. #14
    New Lounger
    Join Date
    May 2015
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hey Zeddy, sorry for the delay. Since I'm the master of the document, I don't really care, I will just clear the content and I'm fine with it.

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    The point I was making was, if you delete the contents of the dropdown cell, you could have the adjacent cell cleared automatically as well.
    Also, if you have already selected say, 3, from the dropdown, nothing happens (as expected, because it's 'already there'). But this means that once you have chosen the sequence of dropdown values (e.g. 3,1,4,2), the you cannot change the order (in the adjacent cell), except by deleting the contents of the dropdown cell, and starting over again.
    You could have it so that, when making a dropdown selection, if it has 'already' been chosen, then picking it again 'removes' it etc etc etc.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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