Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Customized Data Validation (Excel 2000)

    Is it possible to add customized data input validation ? For example, user can only input data in the cell in the following format: "three letters + four digits + two letters" (i.e. BBC3423AB, YZE7431TC ...)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Customized Data Validation (Excel 2000)

    Try adding this formula:
    =(MID(a1,1,1)>="A")*(MID(a1,1,1)<="Z")*(MID(a1,2,1 )>="A")*(MID(a1,2,1)<="Z")*(MID(a1,3,1)>="A")*(MID (a1,3,1)<="Z")*(ISNUMBER(VALUE(MID(A1,4,4))))*(MID (a1,8,1)>="A")*(MID(a1,8,1)<="Z")*(MID(a1,9,1)>="A ")*(MID(a1,9,1)<="Z")

    The formula must be <256 char so you are near the limits so not too many more conditions can be added

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customized Data Validation (Excel 2000)

    Thanks ! This works well. However, what I actually need is "AAAAAAA00" (seven characters plus two numbers). Anyway to get around the 256 character limit ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customized Data Validation (Excel 2000)

    Why didn't you say so in your original question? The solution is to put the validation formula in a cell (which may be in a hidden column).

    Say that you want to validate cell A1. Put the following formula into cell B1:

    =(MID(A1,1,1)>="A")*(MID(A1,1,1)<="Z")*(MID(A1,2,1 )>="A")*(MID(A1,2,1)<="Z")*(MID(A1,3,1)>="A")*(MID (A1,3,1)<="Z")*(ISNUMBER(VALUE(MID(A1,8,2))))*(MID (a1,4,1)>="A")*(MID(A1,4,1)<="Z")*(MID(A1,5,1)>="A ")*(MID(A1,5,1)<="Z")*(MID(A1,6,1)>="A")*(MID(A1,6 ,1)<="Z")*(MID(A1,7,1)>="A")*(MID(A1,7,1)<="Z")*(I SNUMBER(VALUE(MID(A1,8,2))))

    and use =B1 as custom validation formula. You could also create a user-defined function:

    Function IsValidEntry(strVal) As Boolean
    IsValidEntry = (strVal Like "[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]##")
    End Function

    and enter the formula =IsValidEntry(A1) in cell B1, and again use =B1 as custom validation formula.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Customized Data Validation (Excel 2000)

    You got around the issue of not being able to use a custom function in datavalidation with an intermediate value. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Much nicer than mine, if you don't mind the intermediate.

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customized Data Validation (Excel 2000)

    I didn't think of it myself - see MSKB article XL97: Cannot Use Custom Function with Data Validation.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customized Data Validation (Excel 2000)

    Great ! Many thanks.

Posting Permissions

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