Data Validation - Multiple Conditions . HELP!

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
Am trying to get a data validation to work. Has a few conditions.

I want to Validate A1, B1, and C1 as follow

A1 must be either Y or N.
B1 must be either Y or N.
C1 must be a numerical Value.

Only A1 or B1 can have a Y or N at any one time.

If A1 or B1 has a Y or N, C1 can only be 0. If A1 AND B1 are empty, C1 can be any number.

Any ideas?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
On 2002-04-02 09:48, Cosmos75 wrote:



A1 must be either Y or N.
B1 must be either Y or N.
C1 must be a numerical Value.

Only A1 or B1 can have a Y or N at any one time.

a1
=AND(OR(A1="y",A1="n"),A1<>B1)
B1
=AND(OR(B1="y",B1="n"),A1<>B1)
C1
=IF(COUNTIF(A1:B1,"Y")+COUNTIF(A1:B1,"n"),C1=0,ISNUMBER(C1))

Under custom would seem to do what you want. But using this validation, it seems your really limiting what can be put into B1 based on A1 etc. Once N is in A1, B1 can only be unchanged (blank) or Y unless your user blanks out A1 or pastes an item in B1 (which is a limitation of valadation). Just guessing, but it sounds like a more robust vba solution may be needed depending what you want to accomplish...

good luck
 
Upvote 0
I also need it for A1 and B1 such that C1=0 so I tried this:

a1
=AND(OR(A1="y",A1="n"),A1<>B1,C1=0)

It works if there is something in B1. But if I enter a value in C1, and then enter Y or N in A1, I don't get an error message???
This message was edited by Cosmos75 on 2002-04-02 10:36
 
Upvote 0
On 2002-04-02 10:35, Cosmos75 wrote:
I also need it for A1 and B1 such that C1=0 so I tried this:

a1
=AND(OR(A1="y",A1="n"),A1<>B1,C1=0)

It works if there is something in B1. But if I enter a value in C1, and then enter Y or N in A1, I don't get an error message???
This message was edited by Cosmos75 on 2002-04-02 10:36

If I understand you right, the problem isn't with the formula, it is with validation itself. I'm not sure the best way to describe this, but it will only work with the data you have as of the moment you enter it. In other words, if you have validation set up only to allow the value of A1 in a cell and A1 = 4, you can only put a 4 in the cell. But if you know change a1 to 9, you won't get an error for what you previous put in. Does this make sense, or am I totaling missing the point?
 
Upvote 0
Here's some examples:

1. Say
A1 is Empty
B1 is Empty
C1 is equal to 0

If I enter Y or N in A1, I want that to be OK.

2. Say
A1 is Empty
B1 is Empty
C1 is equal to 4

If I enter Y or N in A1, I want data validation to stop me from doing so.

3. Say
A1 is Empty
B1 is equal to Y
C1 is equal to 0

If I enter Y or N in A1, I want data validation to stop me from doing so.

I hope that clears it up. Sorry for not doing a better job of explaining.
 
Upvote 0
A1 must be either Y or N.
B1 must be either Y or N.
C1 must be a numerical Value.

Hi
If these cells are the ones recieving the data then put this in your sheet code mod.

'<Begin VBA>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case Target.Row
Case 1 To 2
If Target.Value <> "Y" And Target.Value <> "N" Then
MsgBox "Invalid Entry - Must Enter 'Y' or 'N'."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Target.Select
Exit Sub
End If
Case 3
If Not IsNumeric(Target.Value) Then
MsgBox "Invalid Entry - Must Enter a Number."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Target.Select
Exit Sub
End If
Case Else: Exit Sub
End Select
End If

End Sub
'<End VBA>

Have a nice day!
Tom
 
Upvote 0
TsTom,

What do I modify to change what you have for columns A:C to a range, for example, A10:C25??

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top