Data Validation with IF Statement

Microsoft Microsoft office excel 2007 ac...
March 17, 2010 at 11:11:27
Specs: Windows XP
I am trying to create a if scenario based on a selection made. For example, I have 3 questions. If question 1 is answered with No (from a drop down list), answers for Question 2 and 3 should automatically change to N/A. If Yes is chosen for question 1, then question 2 and 3 should have a drop down list with the values of Yes and No. Thanks in advance for your assistance!

See More: Data Validation with IF Statement

March 17, 2010 at 12:18:21
This should work for Question 1 in B13 and Questions 2 & 3 in B14 and B15. Adjust as required...

Right Click the Sheet tab for the sheet where you want this to happen and choose View Code.

Paste this code into the pane that opens. Read the comments to see what the code should do.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check To See If Change Was Made To B13
 If Target.Address = "$B$13" Then
'If B13 = No, Delete DropDown and Set B14:B15 to N/A
   If Target = "No" Then
      With Range("$B$14:$B$15")
       .Value = "N/A"
      End With
     Exit Sub
   End If
'If B13 = Yes, Clear B14:B15 and Add Yes-No Drop Downs
    With Range("$B$14:$B$15").Validation
        .Add Type:=xlValidateList, Formula1:="Yes,No"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
 End If
End Sub

Report •

March 17, 2010 at 13:17:16
Thanks DerbyDad03, that worked perfectly!!!

Report •

March 17, 2010 at 13:33:45
I'm glad to have been of assistance!

Report •
Related Solutions

Ask Question