How do I create an IF Statement that contains a data validation option

[jkitterm]

I am filling out a list of capabilities that have sub-capabilities.
A2 is the Capabilty Name
A3 is the Sub-Capability name
B2 is drop down list (data validation ) with selections of "Yes", "No", or "N/A"
In B3 I am trying to create an IF Statement where if B2="YES" then I am presented with a drop down list (Yes, No, or N/A)to select from for B3 or if it is not Yes then leave the cell blank.

[TABLE="width: 248"]
<tbody>[TR]
[TD][/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Capability[/TD]
[TD]Yes/No[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Voice[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Voice Skills Based Routing[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]IVR[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]IVR Call Routing[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]IVR Skills Based routing[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]Automated Surveys[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]Surveys (IVR)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD]Surveys (Web/email/text)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would think the IF statement would look something like this but I am not sure what the syntax is to have it present a drop down list:
=IF( A2="Yes", drop down list,IF(A2="No", "", IF(A2="N/A", "","")))

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Dim Choices As String
    Choices = "Yes, No, N/A"
    If Target = "Yes" Then
        Range("B3").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=Choices
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Else
        Range("B3").Select
        With Selection.Validation
            .Delete
        End With
    End If
End Sub

 

[jkitterm]

Mumps, thanks for this but I can not have any macros in this spreadsheet (nor am I that familiar with using macros)
Would you know the equivalent formula syntax for this?
Thanks again.

 

[mumps]

I am not aware of any formula that will insert a validation list into a cell. If you just want to try the macro to see how it works, just follow my instructions in Post #2 .

 

[jkitterm]

Mumps, Inserted the code you provided. It works when B2 is "Yes". However, when B2 is either "No" or "N/A" the value of B3 remains what it was before (it doesn't go to blank)

 

[mumps]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Dim Choices As String
    Choices = "Yes, No, N/A"
    If Target = "Yes" Then
        Range("B3").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=Choices
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Else
        Range("B3").Select
        With Selection.Validation
            .Delete
        End With
        Range("B3").ClearContents
    End If
End Sub

 

[jkitterm]

That worked.
Now, what would the syntax be if there are two or multiple sub-capabilities (B3 and B4 and B5) under the Capability

I see - Range("B3").Select - which is where I would presume the change would be made.

 

[mumps]

I'm not sure what you mean. Could you please explain in detail, step-by-step, referring to specific cells.

 

[jkitterm]

So just like you previously did but now there are two sub-cells instead of just one which need to change based upon B2.

If B2 ="Yes" I would want a drop down in in B3 and in B4. B2 is "No" or "N/A" I would want B3 and B4 to be blank

Hope that makes sense.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Capability[/TD]
[TD]Yes/No[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Voice[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Voice Skills Based Routing[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]IVR[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]IVR Call Routing[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]IVR Skills Based routing[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]Automated Surveys[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]Surveys (IVR)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]A9[/TD]
[TD="bgcolor: #FAFAFA"]Surveys (Web/email/text)[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[mumps]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Dim Choices As String
    Choices = "Yes, No, N/A"
    If Target = "Yes" Then
        Range("B3:B4").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=Choices
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Else
        Range("B3:B4").Select
        With Selection.Validation
            .Delete
        End With
        Range("B3:B4").ClearContents
    End If
End Sub