Announcement

Collapse
No announcement yet.

VLOOKUP or OFFSET/MATCH or INDEX/MATCH in a Data Validator

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VLOOKUP or OFFSET/MATCH or INDEX/MATCH in a Data Validator



    What I have:
    Sheet 1 has a list of tank_id's (no duplicates) called "Details" (Columns are Tank_id, Tank_Name, Nation, Turret, TArmor_front, TArmor_rear, TView_range..).
    Sheet 2 has a list of Turrets and the specifications, called "Turrets". (Columns are Tank_id, Turret_id, Turret_Name, Armor_front, Armor_rear, View_range.)
    I have created a table of the turrets called tblTurrets and named a range of all the turrets called allTurrets. I have named the Tank_id range as sTank_id_turrets and I have named the Turret_name range as sTurrets
    there are 633 Tanks and 2000+ Turrets..


    What I hope to accomplish:
    I want a data lookup(DL) in $D$3 (the Turrets column) of the "Details" sheet to reference the tank_id in $A$3, of the Details sheet, and then compare it to the tank_id in the "Turrets" sheet. There might be up to 4 matches. Then populate the DL with the turrets names. Then when the Turret name is selected in the DL, to populate the cells to the right of it.

    What I have used:
    =INDEX(sTurrets;MATCH(A3;sTank_id_turret;0)) - works but doesnt get all the matches, only first one.

    =VLOOKUP(A3;tblTurret;sTurrets;FALSE) - named range not found

    =VLOOKUP(A3;allTurrets;sTurrets;FALSE) - currently evaluates to an error

    =VLOOKUP(A3;sTank_id_turret;sTurrets;FALSE)- currently evaluates to an error

    =OFFSET(sTurrets;MATCH($A$3; sTank_id_turret;0);0;COUNTIF(sTank_id_turret;$A$3);1) -
    gets the right amount of matches, but not the first correct one. ie if the turrets names for tank_id 14913 were 't34 mod, Cruiser Mk1, Cz03 LTvz35, Leopard Prototype A1 and Leopard Prototype A2. This formula omits t34 mod and then added in Porsche T169, which is for tank_14914.

    These are used in the formula1 of the DV:

    Code:
     'Turrets
        With Sheet1.Range("M3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=OFFSET(sTurrets;MATCH($A$3; sTank_id_turret;0);0;COUNTIF(sTank_id_turret;$A$3);1)"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    1. What am I missing to load a complete list in order?
    2. Is there a way to load the last value in the list by default?
    3. How do I populate the cells to the right of the DV?
    Any help with this will be appreciated.

  • #2
    Hard to visualise everything, can you attach your workbook.
    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Comment


    • #3
      Sorry, here is the sample attached. Thanks for the reply.
      Attached Files

      Comment


      • #4
        Try the attached file.

        I have changed all the code in the Details Worksheet Object Module to:
        Code:
        Option Explicit
        
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            Dim x, i As Long, lrow As Long, s As String
            lrow = Cells(Rows.Count, 1).End(3).Row
            
            If Target.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("e3:e" & lrow)) Is Nothing Then
                x = [tblTurrets]
                For i = 1 To UBound(x, 1)
                    If x(i, 1) = Target.Offset(, -4) Then
                        If s = "" Then s = s & x(i, 3) Else s = s & "," & x(i, 3)
                    End If
                Next
                With Target.Validation
                    .Delete
                    If s <> "" Then .Add xlValidateList, , , s
                End With
            End If
            
        End Sub
        
        Private Sub Worksheet_Change(ByVal Target As Range)
            Dim x, y(1 To 5), i As Long, ii As Integer, lrow As Long
            lrow = Cells(Rows.Count, 1).End(3).Row
            
            If Target.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("e3:e" & lrow)) Is Nothing Then
                If Target = "" Then
                    Cells(Target.Row, 6).Resize(, 5).Clear
                    Exit Sub
                End If
                x = [tblTurrets]
                For i = 1 To UBound(x, 1)
                    If x(i, 1) = Target.Offset(, -4) And x(i, 3) = Target Then
                        For ii = 1 To 5
                            y(ii) = Sheet2.Cells(i + 1, ii + 3)
                        Next
                        Exit For
                    End If
                Next
                Cells(Target.Row, 6).Resize(, 5) = y
            End If
            
        End Sub
        Attached Files
        We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

        Comment


        • #5
          Thank you so much. How would I change this to accommodate multiple sheets ie. Suspension, radio, guns, ammo ect. I will send the file to you for review.

          Comment


          • #6
            You're welcome.
            We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

            Comment


            • #7


              Just wanted to say thank you for all you did on my project. You went over and above what I expected. Thank you.

              Comment

              Working...
              X