Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    validation limits (XP)

    Need to "validate" cells so the person typing in another person's social security number can only input the last 4 digits of that SSN - also want the result to show as "xxxx-xx-####" -

    I did it last week - and then didn't save the file - apparently lost what was left of my brain over the weekend and can't remember how I did it.

    Again - thank you.

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

    Re: validation limits (XP)

    1) In Format | Cells, set the number format of the cells to a custom format "xxxx-xx-"0000

    2) In Data | Validation, select Whole Number from the Allow list, and specify that the value must be between 1000 and 9999.

  3. #3
    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: validation limits (XP)

    Shouldn't the value be between 1 and 9999?

    I think 0001 is valid but not 0000.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: validation limits (XP)

    <hr>and specify that the value must be between 1000 and 9999<hr>

    Shouldn't the bounds be 0-9999, or perhaps 1-9999? Is the 7th digit of a SSN always >1, or is a zero possible in that space? I am not aware if there are restrictions that would prevent a SSN of -say- 9889-44-0012...

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

    Re: validation limits (XP)

    Yes. you're correct.

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

    Re: validation limits (XP)

    Steve had a similar objection. Thanks.

    (I don't know what the exact 'rules' for a SSN are)

  7. #7
    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: validation limits (XP)

    Here is what Wkikipedia says on the Social Security number.

    It confirms "0001 - 9999" for the 3rd part (serial number).It specifically indicates that xxx-xx-0000 (or even 000-xx-xxxx or xxx-00-xxxx) are indications of invalid numbers

    Steve

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

    Re: validation limits (XP)

    Thanks!

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation limits (XP)

    I've tried every variation each of you suggested - no luck.

    As I said in my original post, I DID accomplish this last week - grrrrrrrrrrrrrrrrr!

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation limits (XP)

    What doesn't work? How about upload what you have and we will take a look.
    Legare Coleman

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation limits (XP)

    I can't even type the 4 digits I'm trying to limit the entry into the cell to . . .

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

    Re: validation limits (XP)

    It works OK, but you should make the column wide enough to display the entire SSN.

    You should set the minimum value in Data | Validation to 1, not 0.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation limits (XP)

    I'm supposed to be able to type, for example, 5555 in that cell and the result should be xxxx-xx-5555 - as it is currently formatted (at least on my computer), if I try to type only 4 digits I get the nasty note I WROTE saying I can't do it!!!!!!

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation limits (XP)

    What happens when you try to enter the four digit number into the cell? I have no problem entering a four digit and it displays as XXX-XX-5555. However, the column is too narrow so it displays as ############. If I make the column wider it displays as expected.

    BTW, you should go to the Error Alert tab in data validation and enter an error message that makes some sense to the operator.
    Legare Coleman

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

    Re: validation limits (XP)

    Sorry, I don't understand that - I can type in any number of 4 digits or less in cells B17:B34 in the spreadsheet you attached - see screenshot.

Page 1 of 2 12 LastLast

Posting Permissions

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