Data Validation - Multiple Conditions . HELP!

[Cosmos75]

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?

[IML]

[quote]
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

[Cosmos75]

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 ]

[IML]

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?

[Cosmos75]

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.

[Tom Schreiner]

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.

'
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
'

Have a nice day!
Tom

[Cosmos75]

TsTom,

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

Thanks!