Here you go (with some help from RomperStomper). Right click on the worksheet tab and "View code" Paste this code in. Modify E1 (cell where original dropdown is) and G1 (cell where secondary drop down is). This also clears G1 each time a new value is selected in E1
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim E1Name As String
If Not Intersect(Target, Range("E1")) Is Nothing Then
E1Name = Target
Range("G1").ClearContents
If IsName(E1Name) = True Then 'Determines if Value in E1 is a defined name
Set Cell = Range("G1")
With Cell.Validation 'Setting up Data Validation in G1
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT($E$1)"
End With
Else
Set Cell = Range("G1") 'Deleting Data Validation in G1
Cell.Validation.Delete
End If
End If
End Sub
Function IsName(strName As String) As Boolean
Dim nme As Name
On Error Resume Next
Set nme = ActiveWorkbook.Names(strName)
IsName = Not nme Is Nothing
End Function
Does that work for you?
Bookmarks