Excel dynamic 'Data Validation' list

You will need to follow along with these tutorials, as you did not provide enough information about the problem to detail an exact solution here.

In short, you will need a table with the left most column being the type selection and the remaining headers being the 24 type selections from the first column and their relevant subselections. Essentially, a crosstab.

You will then need to create a named range called MainList with a formula like...

=INDEX(Table1[[Choose…]],1):INDEX(Table1[[Choose…]],COUNTA(Table1[[Choose…]]))

Where you can replace choose with whatever you want your cell to prompt your user, and Table1 with whatever you named your table with the relevant selection info.

You then need to create a named range called SubList with a formula like...

=IF(OR(Sheet1!B8="Choose…",Sheet1!B8=""),"",INDEX(Table1,1,MATCH(Sheet1!B8,Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX(Table1,,MATCH(Sheet1!B8,Table1[#Headers],0))),MATCH(Sheet1!B8,Table1[#Headers],0)))

You will then need to apply these named ranges as data validation criteria to your given cells (your type column = MainList and your subsequent columns equal to your sublist).

Now, you will need to make sure that your MainList column is directly to the left of the dynamic cell, and if you would like your comboboxes to cascade (ie one column allows selection certain values in another column, ect) you will need your sublist data validation cells directly to left.

If it were me, I would follow along exactly with the comments in the attached linked and have the data input cells in a table. This will enable you to use the worksheet event below which will update the cells if you change something "upstream" from a sublist choice.

Option Explicit

Const CHOOSE = "Choose…"

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Dim targetCell As Range
    Dim nextCell As Range
    Dim oldCalc As Excel.XlCalculation

    If Not Intersect(Target, [DataEntryTable]) Is Nothing Then
        If [Radio_Choice] = 1 Then
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
                oldCalc = .Calculation
                .Calculation = xlCalculationManual
            End With

            For Each targetCell In Target
                'Clear any cells that use 'SubList' to the right of targetCell in the current table.
                If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
                    For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
                        If HasValidationFormula(nextCell) Then
                            If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
                        End If
                    Next nextCell
                End If

                'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
                ' or a 'SubList' dropdown
                If HasValidationFormula(targetCell) Then
                    Select Case targetCell.Validation.Formula1
                    Case "=MainList"
                        If targetCell.Value = "" Then
                            targetCell.Value = CHOOSE
                        ElseIf targetCell.Value = CHOOSE Then
                            'Do nothing.
                        Else
                            targetCell.Offset(, 1).Value = CHOOSE
                        End If

                    Case "=SubList"
                        If targetCell.Value = "" Then
                            targetCell.Value = CHOOSE
                        ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
                            targetCell.Value = ""
                        ElseIf targetCell.Value = CHOOSE Then
                            'Do nothing
                        Else
                            Set nextCell = targetCell.Offset(, 1)
                            If HasValidationFormula(nextCell) Then
                                If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
                            End If
                        End If
                    End Select
                End If
            Next targetCell
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
                .Calculation = oldCalc
            End With
        End If
    End If
    Exit Sub
ErrorHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        If oldCalc <> 0 Then .Calculation = oldCalc
    End With
    MsgBox Err.Description, vbCritical, Name & ".Worksheet_Change()"
End Sub

Private Function HasValidationFormula(cell As Range) As Boolean
    On Error GoTo ValidationNotExistsError
    If cell.Validation.Formula1 <> "" Then
        HasValidationFormula = True
    Else
        HasValidationFormula = False
    End If
    Exit Function
ValidationNotExistsError:
    HasValidationFormula = False
End Function

Credit goes out to

http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

and

http://www.contextures.com/xlDataVal15.html