Conditional trigger of data validation list

[Peter Harms]

Hi there
My problem
1. Column A captures either "yes" or "No' from a data validation list
2. Column B Captures dates from a data validation list
If the response in column A is "No" then I want the date to equal the date in the cell above.
If the response is in column A is "Yes" then I want to trigger the data validation for the dates
Is this possible?
Many thanks

 

[DanteAmor]

Put the following code in the events of your sheet. Change the range "$G$1:$G$8" by the range where you have the dates for the validation list.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If LCase(Target.Value) = LCase("Yes") Then
            With Target.Offset(0, 1).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=[COLOR=#ff0000]$G$1:$G$8[/COLOR]"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        ElseIf LCase(Target.Value) = LCase("No") Then
            If Target.Row > 1 Then
                If Target.Offset(-1, 1) <> "" Then
                    Target.Offset(0, 1) = Target.Offset(-1, 1)
                End If
            End If
        End If
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Aryatect]

Hi Peter,

Without using VBA, This can be achieved by putting putting a below formula in Data Validation:

=IF(A3="No",B2,INDIRECT(A3))

where Yes is the Defined Name of the conditional dates for choosing option "Yes". In this Case if you choose "No" the drop-down will only show the value from above.

Book1
	A	B
1	Response	Date
2	Yes	7/5/2019
3	No	7/5/2019
4	No	7/5/2019
5	Yes	7/9/2019
6	No	7/9/2019
Sheet5

Book1
	J
1	Yes
2	7/5/2019
3	7/6/2019
4	7/7/2019
5	7/8/2019
6	7/9/2019
7	7/10/2019
8	7/11/2019
9	7/12/2019
10	7/13/2019
11	7/14/2019
12	7/15/2019
13	7/16/2019
Sheet5