Can I create multiple data validation rules in a single cell?

[MattinglyC]

Help =)
I am wondering if it is possible (and if so, how) to apply two validation rules to a single cell. For example:

Column A has a data validation to select from a drop down list (Yes, No, N/A).
Column B has a data validation to select from a drop down list (Steroids, NSAIDS, Other).

What I want is to set column B to have a second validation rule =IF(A1="Yes",true,false), so that column B cannot provide the dropdown list/be completed if Column A is No or N/A.

Right now I can set a validation for column b to select from a list, but I don't know how to tell column B to only provide the list if Column A = Yes.

Thanks for the help!!
CMatt

 

[L. Howard]

Hi CMatt, welcome to the board.

Give one of these change event macros a try, copy to the sheet module that has the drop down in column A.
Use ONLY ONE of the change event code in the module at a time, both won't work at all.

The first code is if only A1 has the drop down with the Steroids, NSAIDS, Other choices.
The second code is if 'many' A column cells have a drop down with the Steroids, NSAIDS, Other choices.

You can un-comment the spare MyList()'s to add additional items for the B cell/column drop downs.
As example change from this >> 'MyList(3) = "?" to this >> MyList(3) = "Spinach"
and Spinach will become a choice in the B drop down.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
'// For the cells A1 and B1 only
If Intersect(Target, Range("$A$1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub


Dim MyList(5) As String
MyList(0) = "Steroids"
MyList(1) = "NSAIDS"
MyList(2) = "Other"
'MyList(3) = "?"
'MyList(4) = "?"
'MyList(5) = "?"


Select Case Target.Value


  Case Is = "Yes"


       With Range("B1").Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=Join(MyList, ",")
            [B1].Select
      End With
      
  Case Else
  
      With Range("B1").Validation
           .Delete
           [B1].ClearContents
      End With
      
End Select
      
End Sub

'///*************************************************************************\\\'

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
'// For the column A
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub


Dim MyList(5) As String
MyList(0) = "Steroids"
MyList(1) = "NSAIDS"
MyList(2) = "Other"
'MyList(3) = "?"
'MyList(4) = "?"
'MyList(5) = "?"


Select Case Target.Value
'MsgBox Target.Value


  Case Is = "Yes"


       With Target.Offset(, 1).Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=Join(MyList, ",")
            Target.Offset(, 1).Select
      End With
      
  Case Else
  
      With Range("B1").Validation
           .Delete
           Target.Offset(, 1).ClearContents
      End With
      
End Select
      
End Sub