|
Data validation formula in cell and list?
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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
Very fancy! Thanks i will give it a try, i appreciate the response.
-- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
You are welcome! Thanks for the feedback! Please post the result of the try!
Stefi €žSuzanne€ť ezt Ă*rta: Very fancy! Thanks i will give it a try, i appreciate the response. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
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 -- Thank you -- Suzanne. "Stefi" wrote: You are welcome! Thanks for the feedback! Please post the result of the try! Stefi €žSuzanne€ť ezt Ă*rta: Very fancy! Thanks i will give it a try, i appreciate the response. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
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: 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 -- Thank you -- Suzanne. "Stefi" wrote: You are welcome! Thanks for the feedback! Please post the result of the try! Stefi €žSuzanne€ť ezt Ă*rta: Very fancy! Thanks i will give it a try, i appreciate the response. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
Notice "I71"! Use must use capitals!
Stefi €žStefi€ť ezt Ă*rta: 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: 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 -- Thank you -- Suzanne. "Stefi" wrote: You are welcome! Thanks for the feedback! Please post the result of the try! Stefi €žSuzanne€ť ezt Ă*rta: Very fancy! Thanks i will give it a try, i appreciate the response. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
ah ok got it, again, can't thank you enough.
-- Thank you -- Suzanne. "Stefi" wrote: 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: 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 -- Thank you -- Suzanne. "Stefi" wrote: You are welcome! Thanks for the feedback! Please post the result of the try! Stefi €žSuzanne€ť ezt Ă*rta: Very fancy! Thanks i will give it a try, i appreciate the response. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
Data validation formula in cell and list?
You are welcome! Thanks for the feedback!
Stefi €žSuzanne€ť ezt Ă*rta: ah ok got it, again, can't thank you enough. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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 -- Thank you -- Suzanne. "Stefi" wrote: You are welcome! Thanks for the feedback! Please post the result of the try! Stefi €žSuzanne€ť ezt Ă*rta: Very fancy! Thanks i will give it a try, i appreciate the response. -- Thank you -- Suzanne. "Stefi" wrote: 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: 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) -- Thank you -- Suzanne. |
| All times are GMT +1. The time now is 05:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com