How do I do this and what is it called?

[aherzog]

I am so close to solving my excel problem, but can't seem to break past this final step - someone please help, it has been going on for three weeks now in my search for an answer! I have a workbook where sheet1 is a table of 4 columns, each has a title of Item #, MFG, Model, Qty. Sheet2 is not a table but the first 4 columns in Row 1 each have the same titles as Sheet1. I want a dependent drop down list (or something similar to that) where if I choose the Item # from Sheet1 in Row 4 (for example), then the following drop down list under the MFG Column only shows one possible choice: the MFG information that is also in Row 4. Basically, I want all the following information to show up just off of the choice of one Item #. I have a VBA Code which does the basic function of what I am looking for, which I pasted in Sheet2:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng         As Range
Dim Dn          As Range
Dim Dic         As Object
If Target.Address(0, 0) = "A1" Then
    With Sheets("Sheet1") 
    Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
      End With
        Set Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
For Each Dn In Rng: Dic(Dn.Value) = Empty: Next
With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
End With
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dn              As Range
Dim Rng             As Range
Dim Dic             As Object
Dim n               As Integer
If Not Intersect(Target, Range("A1:D1")) Is Nothing Then
  Application.EnableEvents = False
   For n = Target.Column To 4
        Target.Offset(, n).Validation.Delete
        Target.Offset(, n).Value = ""
   Next n
Application.EnableEvents = True
With Sheets("Sheet1")
Set Rng = .Range(.Cells(2, Target.Column), .Cells(Rows.Count, Target.Column).End(xlUp))
End With
  Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   If Not Rng(1).Offset(, 1) = "" Then
   For Each Dn In Rng
       If Dn.Value = Target.Value Then
         If Not Dic.exists(Dn.Offset(, 1).Value) Then
                 Dic(Dn.Offset(, 1).Value) = Empty
            End If
        End If
      Next Dn
   
If Dic.Count > 0 Then
 With Target.Offset(, 1).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
End With
End If
End If
End If
End Sub

There are two problems with this code, which is what I need help with:
1. The code starts the first drop down list in Row 1 (where the titles are) - I need it to start the first dependent drop down list from Row 2.
2. It only gives me one row of drop down lists. I need several, as this is for a list of inventory that needs to be ordered in the store and I'm sure I will need to order more than just one item. I need this code to work for at least 20 rows (if not more).

You can find a link to a dummy workbook for this question at another thread which I had posted on before:
Drop down lists dependent on each other - is it possible?

I would be forever grateful for anyone who can help me solve this problem! THANKS!