CODEWITHSUNDEEP
vbaexcel
ewitkows
ewitkows

Reputation: 3618

Create a data validation in a cell based on the value in another cell

I have 2 columns in a spreadsheet, both are picked from a drop down list.

Col A is "Do you want more information" - Possible Values Yes\No

Col B is "How did you hear about us" - Possible values are Email\Phone\Other

Question: If "No" is chosen in Col A, then Col B's value should be N/A, otherwise the user should be able to choose from the dropdown and pick a value. Using the following formula, I can get N/A to appear, but the "false" option overwrites any available dropdown values or previously selected values. So simply put, if Yes is chosen, let user choose a value in the other cell. If No is chosen, show N/A in the other cell. Thoughts on how I can get this to work?

My current formula that doesn't work (which resides in cell A2):

=IF(A1="No","N/A","Select Value")

Upvotes: 0

Views: 20743

Answers (3)

Muddler
Muddler

Reputation: 13

This is the solution for my task. The code lets us change the data validation in one cell depending on the datavalidated input in the previous one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L21")) Is Nothing Then
    Worksheets(3).Range("L23").Clear
    Select Case Range("L21")
        Case "x": x_projection
        Case "y": y_projection
        Case "z": z_projection
    End Select

End If
End Sub

Sub x_projection()

    Worksheets(3).Range("L23").Validation.Delete
    Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="y, z"
    Worksheets(3).Range("L23") = "z"

End Sub

Sub y_projection()

    Worksheets(3).Range("L23").Validation.Delete
    Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="x, z"
    Worksheets(3).Range("L23") = "x"

End Sub

Sub z_projection()

    Worksheets(3).Range("L23").Validation.Delete
    Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="x, y"
    Worksheets(3).Range("L23") = "y"

End Sub

Upvotes: 0

CRondao
CRondao

Reputation: 1903

Just for the fun of it, because it is not a complete solution, that will need a litle vba... Firs ComboBox in A1 (Yes/No), in D1:D3 (Email/Phone/Other), in E1 (NA). Write as source for B1 list Data Validation:

=CHOOSE(IF(A1="Yes";1;2);D1:D3;E1)

You need VBA, for, at least, delete the value in B1 when the value in A1 changes, and change it to "NA" if A1=No

Upvotes: 0

WGS
WGS

Reputation: 14179

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Target.Column = 1 Then
        Set Cell = Target.Offset(0, 1)
        If Len(Target.Value) = 0 Then
            Cell.Validation.Delete
            Cell.Value = vbNullString
        Else
            If Target.Value = "Yes" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=ContactMethod"
                End With
            ElseIf Target.Value = "No" Then
                Cell.Validation.Delete
                Cell.Value = "N/A"
            Else
                MsgBox "Input only Yes or No."
                Target.ClearContents
                Cell.Validation.Delete
            End If
        End If
    End If
End Sub

Screenshot:

If Yes is entered in Column A (note the dropdown arrow):

enter image description here

If No is entered in Column A (note the now missing dropdown arrow):

enter image description here

If anything else is entered in Column A:

enter image description here

In addition, it safely deletes the validation if Column A's value is deleted or changed to No. This way, there's no way to access the dropdown unless Column A is specifically Yes.

Make sure to paste it in the sheet's module (Sheet9 in the screenshot below):

enter image description here

Let us know if this helps.

Upvotes: 1

Related Questions