Excel dynamic 'Data Validation' list

Question

I am using an excel sheet to aid in configuring large xml files.

I have the following

Where of the 24 different Type options, they share a large amount of common fields to configure (The yellow cells, of which there are 15 columns, followed by 36 more columns that mostly apply to all 24 types.)

Finally I have 21 columns which are just labeled as Option1, Option2... due to their cell having a different translation per Type:

Where that image is of the key just as a reference.

When actually configuring items, I have used Data Validation (as seen in image 1) to select the Type which then displays the variable type in the OptionX cell as referenced from the Options key (A specific note here: As a reminder to the user, I would like the value to populate each cell as appropriate while still applying a Data Validation list)

The following is where I have trouble.

I would like to have each option conditionally apply a Data Validation list when needed. So essentially a user selects the Type which then populates OptionX and depending on the value in that Option cell, will bring up the appropriate Data Validation list (if one is applicable, as some options are based on ID numbers ect... in which case I would not have a list or at the very most an empty one)

(Another side note: I have not used excel since middle school- so if some of my methods are in poor form, I apologize. As I was researching before asking this question I noted something about many INDIRECT calls are bad and I currently have many. Each Option contains a formula along the lines of

=VLOOKUP(  OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-54),Sheet2!A16:B39,2,FALSE)

in order to reference the value of Type so that I could just copy/paste the formula down ~500 rows (average number of items to configure per project) though I have suspicions that Excel would be able to handle this in a more graceful way.)

I have improved the cells formula by replacing the first with:
=VLOOKUP( D3,'smart MDR710 config.xlsx'!options,2,FALSE)
and copy/pasting that to the next 499 cells

Answer 1

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