Data validation formula in cell and list?

[Suzanne]

Good morning. I'd like to have formula in B2 that returns NA if B1 is No, if
B1 is Yes, i'd like to force drop down selection or validation in B2 of
Yes/No - is this possible?

A B
1 Was it cold enough to snow? Yes/No
2 Did it snow? =if(B1="No",NA)

 

[Stefi]

You need an event sub for that, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B1" Then
If Target.Value = "No" Then
Range("B2").Validation.Delete
Range("B2").Value = "NA"
Else
Range("B2").Value = ""
Range("B2").Validation.Delete
Range("B2").Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No"
Range("B2").Select
End If
End If
End Sub

Regards,
Stefi


„Suzanne†ezt írta:

 

[Suzanne]

Very fancy! Thanks i will give it a try, i appreciate the response.

 

[Stefi]

You are welcome! Thanks for the feedback! Please post the result of the try!
Stefi

„Suzanne†ezt írta:

 

[Suzanne]

Again thanks for your help, this is quite new to me. I pasted your code in
my vb editor and made the adjustments to the target cells:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "i71" Then
If Target.Value = "No" Then
Range("i72").Validation.Delete
Range("i72").Value = "NA"
Else
Range("i72").Value = ""
Range("i72").Validation.Delete
(HERE) Range("i72").Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No" (-- TO HERE APPEARS IN RED
FONT
Range("i72").Select do i still need
a list or the list is the
End If "Yes,No"
in the code? THANKS!)
End If
End Sub

 

[Stefi]

The forum's editor can't handle long code lines properly, I inserted a line
separator to split up long line:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "I71" Then
If Target.Value = "No" Then
Range("I72").Validation.Delete
Range("I72").Value = "NA"
Else
Range("I72").Value = ""
Range("I72").Validation.Delete
Range("I72").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No" 'this is the list built in the
code
Range("I72").Select
End If
End If
End Sub

Don't forget to paste the code in the worksheet code window (NOT in a normal
module!)

Stefi


„Suzanne†ezt írta:

 

[Stefi]

Notice "I71"! Use must use capitals!
Stefi


„Stefi†ezt írta:

 

[Suzanne]

ah ok got it, again, can't thank you enough.

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi


„Suzanne†ezt írta: