Cell based dropdown list

[sahana108]

Hello All,
I am looking for a formula or macro,

If I select Yes in "D2", a dop down has to come in "C4", if I again change the D2 to N/A the C4 has to change to "N/A" automatically

I have tried with name manager, the problem is when we change the parent cell drop-down list getting updated and leaving the text what we have selected last time.

I need only when I select N/A the destination has to N/A if I select Yes a drop-down of Yes No and if I select again N/A the drop-down to disappear and N/A should come.

Would appreciate if any suggestions. Thank you..!!

 

[Crystalyzer]

this code in the Worksheet Change event should do the trick

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, add1 As String, add2 As String
    
    Application.EnableEvents = False
    Set r = Intersect(Target, Range("D2:G2"))
    
    If r Is Nothing Then Exit Sub
        
    If Target.Value = "N/A" Then
        With Range("C" & Target.Column)
            .Validation.Delete
            .Value = "N/A"
        End With
    Else
        With Range("C" & Target.Column)
            With .Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="N/A, Yes"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
            .Value = "Yes"
        End With
    End If
        
    Application.EnableEvents = True

End Sub

 

[sahana108]

this code in the Worksheet Change event should do the trick

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, add1 As String, add2 As String
   
    Application.EnableEvents = False
    Set r = Intersect(Target, Range("D2:G2"))
   
    If r Is Nothing Then Exit Sub
       
    If Target.Value = "N/A" Then
        With Range("C" & Target.Column)
            .Validation.Delete
            .Value = "N/A"
        End With
    Else
        With Range("C" & Target.Column)
            With .Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="N/A, Yes"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
            .Value = "Yes"
        End With
    End If
       
    Application.EnableEvents = True

End Sub

Thanks a lot

Crystalyzer​

 

[Crystalyzer]

Happy to help!