I'm trying to set up two dependent data validation in a worksheet. Based on the selection in the first data validation cell, the second cell data validation list is adjusted.
I've had a look at INDIRECT () function in other tutorials but my data is not set up to allow the use of that function so I'm turning to VBA as an alternative.
In a simple Test sheet:
First data validation cell has a dynamic named range in C3: =OFFSET(Data!$B$4,,,COUNTA(Data!$B$4:$B$30))
Second data validation cell is created by this code in VBA in E3 (that I have found in another tutorial) that basically pulls the corresponding dynamic named range based on selection in C3.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Set Rng = Range("C3") If Not Intersect(Target, Range("C3")) Is Nothing Then With Range("E3").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & Rng .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Rng.Select End If End Sub
So far it works only in E3 depending on the choice in only C3......but if I have data validations going down to, say, C10....how can I populate the second data validation (by changing range in VBA??) to, for example, E10?