Gingertrees
Well-known Member
- Joined
- Sep 21, 2009
- Messages
- 697
Summary: Excel 2010, password-protected workbook with data validation in cells, that is not doing its job of rejecting non-compliant entries.
I've created numerous protected workbooks for use by less-than-computer savvy users, who fill them out and email them back to me. I protect the heck out of these forms due to this fact. Yet, for reasons unknown, some of the simplest protections - basic data validation - sometimes goes awry.
For example, one of the fields is "phone number", data validation setting is simply text length=10. Yet I received a workbook today with text length 12 (someone had added the hyphens into the 10-digit number).
In another field, I have a more complex custom data validation. It's a custom ID, in which the first character of the field must be the letter "A", and it must have a total character length of 6 or more. That one's in cell Z2:
=AND(LEN(Z2)>5,LEFT(Z2,1)="A")
Yet I've had that one sent in with just 2 characters, the first of which being a "J"!
Has anyone else experienced people somehow circumventing data validation like this? The sheets are password protected, and show no evidence of tampering like they got a program to unlock them. And the folks who have done this would NOT know how to intentionally disable these protections.
I've created numerous protected workbooks for use by less-than-computer savvy users, who fill them out and email them back to me. I protect the heck out of these forms due to this fact. Yet, for reasons unknown, some of the simplest protections - basic data validation - sometimes goes awry.
For example, one of the fields is "phone number", data validation setting is simply text length=10. Yet I received a workbook today with text length 12 (someone had added the hyphens into the 10-digit number).
In another field, I have a more complex custom data validation. It's a custom ID, in which the first character of the field must be the letter "A", and it must have a total character length of 6 or more. That one's in cell Z2:
=AND(LEN(Z2)>5,LEFT(Z2,1)="A")
Yet I've had that one sent in with just 2 characters, the first of which being a "J"!
Has anyone else experienced people somehow circumventing data validation like this? The sheets are password protected, and show no evidence of tampering like they got a program to unlock them. And the folks who have done this would NOT know how to intentionally disable these protections.
