Data Validation limitations and oddities

[JenniferMurphy]

In another thread, it was suggested to me to give Data Validation a try as a way of reducing the need to do such extensive data checking in the UDF. So I did a little studying. It looks like it will make my life easier, but, as if often the case with Microsoft products, it comes with some puzzling gotchas, too.

Here are the limitations and oddities that I encountered. Please let me know if I misunderstood something.

1. All of the numeric settings (Whole number, Decimal, Date, Time, & Text length) require a range to be specified. So there is no way to specify that it is “any integer”. I have to specify a range even if any integer is OK with me. Setting “not between” to “1” and “1” will cause an error for “1”, even though “1” is not between “1” and “1”.
2. When a new validation setting is assigned, it will not check the existing cell contents, so as long as it is left alone, invalid data will remain in that cell. I consider this a bug.
3. In a cell with a Text length validation rule, Excel will evaluate a formula and it will pass the validation test if the length of the result is within the specified range. The formula can be any length. I guess this is right, but it's weird (to me).
4. The Date rule will allow date+time for any date in the range except the last one. I guess they are testing that it is <= the end date and not < the end date + 1. This is technically wrong as 11 pm on the last date is still the last date. I consider this a bug.
5. The List option is handy, but, sadly, lacks an auto-fill capability. I have to type the complete selection, click on the dropdown, or press Alt+Down Arrow and scroll to the desired selection. I consider this a design bug and a fairly serious one.

I have often suspected that the development team at M$FT was run by Greek gods who are famous for inventing eternal tortures such as for Prometheus & Sisyphus.

PS: I just discovered that checking the Circle Invalid Data option will circle data that is invalid, but was entered before the validation rule was applied.

 

[JenniferMurphy]

You can't debug them when called by DV.

Why?????

 

[JenniferMurphy]

You can choose "Whole number" and then select "not equal to" and then specify a number that is so large it would never be entered (9999999999 for example).

I actually thought of that, but I hate solutions like that. They are workarounds for poor interpreter design and they are not bullet-proof.

By the way, you missed one of "Data Validations" biggest (to me) limitations which (to me) makes it somewhat worthless (and a reason to choose event coding over "Data Validation")... you can copy/paste "bad" values into a cell with "Data Validation" and that "bad" value will be accepted even if it does not meet the data validation rule for that cell.

I discovered that after my post, but decided it was in the same class as existing bad data not getting flagged when the validation rule is created. But you are right, this is worse.

The Greek Gods really went crazy with this feature. Kinda like the pesky "feature" of this board of making me wait 3 seconds between giving "thanks" and "like".

 

[Rick Rothstein]

Kinda like the pesky "feature" of this board of making me wait 3 seconds between giving "thanks" and "like".

The one I hate about this forum is that you have to wait 20 seconds between searches. Why??? It is really annoying to see the "you have to wait" message ending with "Please try again in 1 second".

 

[JenniferMurphy]

The one I hate about this forum is that you have to wait 20 seconds between searches. Why??? It is really annoying to see the "you have to wait" message ending with "Please try again in 1 second".

I haven't run into that one. I guess I too slow to trigger it. If I had run into it when first checking out Excel boards, I might well have gone elsewhere.

I guess they are trying to deal with bots? But if I were writing anti-bot code and trying to keep the cure from being worse than the disease, I would keep track of inter-search times for a few searches (not just 2). If an account has some history of rapid searches, then I would put some limits on that account. Plus, 20 seconds is way too long for a bot and way too short for most humans.

But, then, running an online forum is probably a lot more challenging than I can imagine.

 

[RoryA]

Why?????

Who knows?