PDA

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