Hi Kendo Team,
While working with the spreadsheet's custom validation feature, my team stumbled on what seems to be a bug with the AND function when checking cell values. When checking the LEN() of a cell's value before an ISNUMBER() check, the validation will sometimes completely ignore the ISNUMBER. This bug does not seem to happen in every situation but when it does it can be nearly impossible to pinpoint in one's own code without accidentally fixing it by changing the order of logic. I've managed to consistently replicate what I'm experiencing in the dojo link below:
The dojo link contains your spreadsheet validation demo -- the only thing that has changed from the original demo is the validation in cells D3 and D4. Both are observing cells F3 and G3, checking that F3 is a number and that G3 is empty. The only thing different about the validation in cells D3 and D4 is the order in which we check the other two cells within the AND function. However, you can note inconsistent behavior here the moment you run it. You will see that while both D3/D4 cells become flagged if G3 is not empty, D3 does not seem to check whether or not F3 is a number. Putting a non-numeric string into F3 or leaving it empty will cause D4 to display the warning but not D3.
So -- bug or am I overlooking something obviously wrong?