validation limits (XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Shouldn’t the value be between 1 and 9999?

I think 0001 is valid but not 0000.

Steve

Reply | Quote

Yes. you’re correct.

Reply | Quote

[indent]

---

and specify that the value must be between 1000 and 9999

---

[/indent]

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…

Reply | Quote

Steve had a similar objection. Thanks.

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

Reply | Quote

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

Reply | Quote

Thanks!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Can’t understand it either – see attached screenshot of MY screen

Reply | Quote

See Legare’s reply – you must have attached the wrong version of the workbook.

Reply | Quote

Nope – same exact one. . . .

Am ready to give up and let the knothead who DID design this spreadsheet deal with the problems.

Thanks to all of you who responded – again.

Aunt Linda

Reply | Quote

Try downloading the one you uploaded and enter something into that.

Reply | Quote

Then you are apparently not uploading the same workbook you are testing with. The workbook you uploaded does allow a 4 digit number to be entered, and the data validation does not have a user entered error message.

Reply | Quote

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.

Reply | Quote

IF I ever get it to work for me, I’ll change the message so it makes sense – this is just a test – and . . . BTW . . . I’m only doing this as a favor to the 1000 secretaries who will have to use this piece o’ crap that I DID NOT design . . .

Thanks for the comments

Reply | Quote