Validation fails even though it passes

[harpbelle]

The topic sounds very confusing but it is true. I have a formula for validating cells that will return the result of "TRUE" if formula is pasted on a cell, but when entered in the validation field, it will give an error that the validation failed after you typed in the text that should pass the validation. Any idea why this could happen?

Failvalidation.png

Here's my sample.

[hrlngrv]

The explanation is that 1) you're using Office 365 which no longer requires traditional [Ctrl]+[Shift]+[Enter] array formula entry, but 2) your formula involves a term, ROW(INDIRECT("1:"&3)), which evaluates to an array. While worksheet formulas like that in Sheet1!E2 don't require traditional array formula entry, data validation formulas STILL can't handle arrays reliably. You need to keep things scalar in data validation formulas. Try

Please Login or Register  to view this content.

There are advantages to using multiple nested IFs. If D2 isn't exactly 16 characters, why bother checking anything else? If it is, but the 1st 2 characters aren't "VB", why bother checking anything else? And so on. In contrast, using AND checks every single term because Excel's AND function isn't smart enough to use short circuit boolean evaluation.

Some day in the distant future, Excel may FINALLY have regular expression functions as LibreOffice Calc and Google Sheets have had for years, but not just yet. Today in Google Sheets you could use =REGEXMATCH(D2,"^VB\d{6}-\d{4}[a-z]{3}$"), but the Excel developer team will have to get over NIH Syndrome for something like this to make it to Excel.

[harpbelle]

Thanks. Best solution by far. I don't know why most validation formulas on the net for specific characters rely on arrays when they don't always work.

[romperstomper]

Your original validation works if you uncheck the 'Ignore blank' option in the DV settings.

[harpbelle]

If you save and reopen the file after unchecking Ignore blank, the validation fails again when you try to type in the cell and exit the cell.

[romperstomper]

So it does. How odd! Apologies for the mis-steer.

[hrlngrv]

The validation formula also works, temporarily, by appending +0 to it.

A guess: since validation works with cell values without triggering circular recalculation, the calculation engine for validation is different than the engine for worksheet formulas. I figure it could work differently if validation formulas are defined before or after the cell contains anything, and reopening workbooks effectively resets validation evaluation context to the same state as if the cell(s) were blank.

Whatever. Excel validation is unreliable when using arrays in data validation formulas.