Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Data Validation - Multiple Conditions . HELP!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TsTom,

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

    Thanks!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •