How to make Dependent drop-down list from a dynamic column.

[Jemini Jimi]

I manually use data from one column to create many columns to create a dependent drop-down list.
However, because the data will always be changing, I want to be able to do this with VBA or a Formula.

This might help. Column C has a numbers (5,35,60,85,90) associated with the dropdown header and Column D numbers are associated with the dropdown items.

 

[Sam_D_Ben]

Were columns C and D added as helper columns, or are they an integral part of the data?

 

[Jemini Jimi]

They are an integral part of the data.

 

[Sam_D_Ben]

Please try this,

Sub TransposeData()
    Dim vA As Worksheet, vB As Long, vC As Long, vD As Integer, vE As String
    Dim vF As String, vG As String, vH As Long, vI As Range, vJ As Range
    
    Set vA = ActiveSheet
    vB = vA.Cells(vA.Rows.Count, 5).End(xlUp).Row
    vD = 21
    
    Set vJ = vA.Range("U34:Z" & vB)
    vJ.Clear
    
    vC = 35
    
    Do While vC <= vB
        vE = vA.Cells(vC, 3).Value
        vF = vA.Cells(vC, 5).Value
        
        If vE <> "" Then
            If vA.Cells(vC + 1, 3).Value <> "" Then
                vA.Cells(34, vD).Value = vF
                vA.Cells(34, vD).Font.Bold = True
                vD = vD + 1
            Else
                vA.Cells(34, vD).Value = vF
                vA.Cells(34, vD).Font.Bold = True
                vH = vC + 1
                
                Do While vA.Cells(vH, 3).Value = "" And vH <= vB
                    vH = vH + 1
                Loop
                
                Set vI = vA.Range(vA.Cells(vC + 1, 5), vA.Cells(vH - 1, 5))
                vI.Copy
                vA.Cells(35, vD).PasteSpecial Paste:=xlPasteValues
                
                vD = vD + 1
            End If
        End If
        
        vC = vC + 1
    Loop
    
    Application.CutCopyMode = False
    MsgBox "Data Updated", vbInformation, "Done"
End Sub

 

[Jemini Jimi]

Sam ur the MAN!
That works perfectly. Thank you so much!!!

 

[Sam_D_Ben]

Sam ur the MAN!
That works perfectly. Thank you so much!!!

No Problem