OS Mojave LO 6.2.5.2
Hey all, can’t figure out where I’ve gone wrong with this document; cells in yellow have a data validity IF(AND( condition that, as far as I can tell, isn’t being met, yet the programmed “stop” action/error message, which is dependent on these conditions being met, still occurs when trying to enter information into these cells.
Cheers.
Your formula expression results always in FALSE because LEN($A$3)=0 is always FALSE because there’s content in A3. Btw, the IF() in IF(AND(LEN($A$3)=0;LEN($C$3)<=2)) is unnecessary, AND($A$3="";LEN($C$3)<=2) already returns TRUE or FALSE.
You probably want to use relative row addressing in the validity formula instead, so in row 3 cell A3 is addressed and in row 4 cell A4 and so on, use $A3 instead of $A$3 when entering the validity over a cell range. However, it won’t work anyway, because your validation hits the current formula being edited of which the result is used in the validation. That design asks for trouble. What effectively is checked in that case is the length of the formula entered, not the result of the formula if it was interpreted. With LEN($C$3)<=2 that always yields FALSE. Rather separate the name input and use another column for that that you concatenate in the formula than editing the formula,
Also the validation in column A doesn’t make sense as is, it suffers from the same wrong absolute row reference.
The same as for IF() (with skipped both ThenValue and OtherwiseValue parameters because it can be optional) applies to IFS(C3<>"";A3="") in conditional formatting. Why not just AND(C3<>"";A3="") ?