If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Validation Limits
I used Data validation to custom zip code because I needed
to limit the numbers. Some numbers started with 00 and if I used the number or text choice the validation did not work. Two questions: Is there any way to limit the length of the number to 5, similar to limiting text length? Can I attach an error message if a number that is typed in the cell isn't a number that is in my validation list, if so how? Thanks in advance. |
#2
|
|||
|
|||
Data Validation Limits
Hi
for the first question try the following: - preformat the cells as 'Text' - limit the length of these cells. As you have Zip-Codes you probably don't need to calculate with them. So store them as 'Text' entries Second question: - if your validation list has the name 'validate' (goto 'Insert - Name - Define' to create such a name) and you use the cell B1 enter the following formula for data validation: =COUNTIF(validate,B1)0 -- Regards Frank Kabel Frankfurt, Germany wrote: I used Data validation to custom zip code because I needed to limit the numbers. Some numbers started with 00 and if I used the number or text choice the validation did not work. Two questions: Is there any way to limit the length of the number to 5, similar to limiting text length? Can I attach an error message if a number that is typed in the cell isn't a number that is in my validation list, if so how? Thanks in advance. |
#3
|
|||
|
|||
Data Validation Limits
Your question is a bit confusing, if you have a list of all the zip codes
then you don't need anything else, everything that is not on that list will return a message if you set it up that way (look at the tabs input message and error alert) Otherwise you can use custom and =LEN(B1)=5 or =LEN(B1)=5 depending on what you mean by limit (up to 5 characters or less than or equal to 5) however if the zips are for the US a zip with a leading zero will return an error message, so I suggest you pre format (under formatcellnumbers) input cell to text so you can use leading zeros.. -- Regards, Peo Sjoblom wrote in message ... I used Data validation to custom zip code because I needed to limit the numbers. Some numbers started with 00 and if I used the number or text choice the validation did not work. Two questions: Is there any way to limit the length of the number to 5, similar to limiting text length? Can I attach an error message if a number that is typed in the cell isn't a number that is in my validation list, if so how? Thanks in advance. |
Thread Tools | |
Display Modes | |
|
|