View Full Version : Yes/No Validation List with One Condition
Shaolin
01-29-2008, 08:44 AM
It's actually simple to explain what I want to do
Let's say that in column M from row 1 to 10 I have a list which has two choices (either yes or no). If Yes is chosen, then it is fine, if No is chosen then column N in the corresponding row cannot be blank. To reiterate, if I choose No in cell M3, then I must write something in cell N3 or else I want an error.
Is there a simple solution?
Thanks
Bob Phillips
01-29-2008, 08:48 AM
In M1, Data Validation, Allow type of Custom and a formula of
=OR(M1="Yes",AND(M1="No",N1<>""))
and uncheck the Ignore Blank checkbox.
You can add whatever error message you want.
Copy M1 down to M10.
Shaolin
01-29-2008, 08:56 AM
In M1, Data Validation, Allow type of Custom and a formula of
=OR(M1="Yes",AND(M1="No",N1<>""))
and uncheck the Ignore Blank checkbox.
You can add whatever error message you want.
Copy M1 down to M10.
I get an error that reads
"The List Source is a delimited list, or a reference to a single row or column."
Shaolin
01-29-2008, 09:05 AM
Ahh, I put it to custom and it works, but it seems like it works to well.
First, if I need the list to be active and choosing custom does not work
Second, when I typed No in M1, a window pops up right after I hit enter saying that a user restricted that value or something similar to that.
Bob Phillips
01-29-2008, 09:11 AM
Yeah, I reversed the process logic. You need to enter a value in N1 before putting No in M1.
Shaolin
01-29-2008, 09:14 AM
Yeah, I reversed the process logic. You need to enter a value in N1 before putting No in M1.
What if I were to type that equation in N1 validation list? Would that work
Bob Phillips
01-29-2008, 09:15 AM
Nope!
Shaolin
01-29-2008, 09:18 AM
Nope!
I didn't think so. Is there a way to keep the list and customize the validation list?
Shaolin
01-29-2008, 09:48 AM
It's giving me an error for both yes and no
I need help
Shaolin
01-29-2008, 09:50 AM
Is there VBA code that would help better for conditional validation list?
If M1 reads Yes, then N1 is fine blank or unblank
If M1 reads No, then N1 must have some data
But I want to retain the validation list (pull down YES/NO) in column M
rajesh nag
03-24-2008, 11:57 PM
Add formula =m2 in coulmn IV2
Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "IV" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
With ActiveCell
If .Row > 1 Then
If Me.Cells(.Row, WS_RANGE).Value = "No" Then
If Sheet1.Cells(.Row, "N").Value = "" Then
MsgBox "Please enter value in coulmn N"
.Value = ""
End If
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.