Multiple Data Validation
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Multiple Data Validation

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    ITALY
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi all,
    I wish I could have multiple Data Validation for one cell.
    The conditions I need to check are :
    - That the value entered is at least 2 digits long
    - That its numeric value is less than the value of another cell (constant).
    Depending on any of the two conditions I need to have different message boxes.
    Thanks in advance for your kind advice.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The first part is easy. Use the Custom Validation and the AND function. Something like =AND(Len(Cell)>1,Cell>RefCell). Getting two different messages is more challenging and prbably would best be done with the WorksheetChange Event.

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    ITALY
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your quick answer.

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    ITALY
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your quick answer.
    I still have problems using your formula when I enter values 01 to 09 in my cell.
    They're converted to 1-9 and I get the error box.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,799
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-29 08:18, Pisolippia wrote:
    Thanks for your quick answer.
    I still have problems using your formula when I enter values 01 to 09 in my cell.
    They're converted to 1-9 and I get the error box.
    Adjust lenze's formula to suit your situation:

    =AND(LEN(Cell)>1,Cell+0

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    ITALY
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    We're still not there.
    Let try to explain better :
    Input cell A1 has your suggested data validation formula :
    AND(LEN(A1)>1,A1+0<$A$2)
    Cell A2 has a constant value say 70
    if I enter values in the range 10-69 they're
    accepted.
    If I enter values from 70 over I get the message box.
    If I enter values from 01 to 09 (two digits) it looks they're missing the zero hence their length becomes < 2 and I get the message box which is not right.

  7. #7
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Format A1 as Text. The "+0" in Aladin's formula converts it back to a number

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    ITALY
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great !
    It works just as expected; will check again to see if someone has any ideas on how to get different message boxes for the same data validation.
    Thanks and Happy Easter to you all.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,799
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-29 08:30, Pisolippia wrote:
    We're still not there.
    Let try to explain better :
    Input cell A1 has your suggested data validation formula :
    AND(LEN(A1)>1,A1+0<$A$2)
    Cell A2 has a constant value say 70
    if I enter values in the range 10-69 they're
    accepted.
    If I enter values from 70 over I get the message box.
    If I enter values from 01 to 09 (two digits) it looks they're missing the zero hence their length becomes < 2 and I get the message box which is not right.
    That's right.

    If you insist entering the numbers as 01, 09, 11, etc., format A1 as Text. Then the formula

    =AND(LEN(A1)>1,A1+0<$A$2)

    will simply work.

    If you format A1 as General, you can use a much simpler formula:

    =A1
    Aladin

  10. #10
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Aladin is correct. If you must display the cells as 05,09,etc., then you can use Custom Formating '00' to display the leading zero for numbers less than 10. This will eliminate the LEN() part of the formula, reducing it to A1
    [ This Message was edited by: lenze on 2002-03-29 09:02 ]

    [ This Message was edited by: lenze on 2002-03-29 09:03 ]

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
  •  

 

 
DMCA.com