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
Formula:
=IF(LEN(D2)=16,IF(EXACT(LEFT(D2,2),"VB"),IF(MID(D2,9,1)="-",IF(COUNT(-REPLACE(MID(D2,3,11),7,1,"")),
IF(ABS(CODE(LEFT(RIGHT(D2,3),1))-109.5)<13,IF(ABS(CODE(LEFT(RIGHT(D2,2),1))-109.5)<13,IF(ABS(CODE(RIGHT(D2,1))-109.5)<13,1)))))))
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.
Bookmarks