In Dynamic Tables, I describe a method that I used to show errors in a data entry table using conditional formatting. I had a small problem with that method that I didn’t address in that post, and now that problem just doubled.
My original problem had to do with the order in which the user would enter data. There is one key field, Invoice, and if that field was empty, but there was anything else in the row, it would show an error. The problem is that Invoice is not the first field to be filled in, the Customer is. The user fills in a customer id and the row turns red because there’s data that’s not an invoice. There really isn’t an error, though, it’s just that the user hasn’t made it to the second field yet (which is the Invoice field). A simple fix, you say, make Invoice the first field. For various reasons, include my lack of a spinal cord, I’m not willing and/or able to change the order of the columns. So I lived with it and so did the users. I suspect that a little piece of the user died each time the row turned red, and it was all my fault.
The doubling occurred when I discovered another necessary bit of data validation that wasn’t included. The user has to enter a Customer, Invoice, and some other information. They must enter exactly two pieces of other information. No more. No less. It’s fairly trivial to do a COUNTA function on the other fields and make sure it’s two. However, it presents yet another timing problem. The user can hardly enter two pieces of information without entering the first piece. And once they do that, it’s red again.
I need a way to determine which row the user is on, and suspend any error messages until they’ve left that row. I thought about moving the whole darn thing to VBA, but I like the formula/conditional formatting approach because it’s quick and other people can maintain it if necessary. I use the CELL function to determine the active row and incorporate that into the CF formula. The upside is that I can omit the second argument, reference, from the formula and it will act on the active cell. The downside is that CELL becomes (or always is) a volatile function. Volatile functions recalculate whenever the worksheet recalculates whether any of their dependencies changed or not. Basically, much of the energy spent on recalculating volatile functions is wasted because the result doesn’t change.
That seems like a winner, except for one little hitch. Moving the cursor around an Excel worksheet doesn’t force a recalc. A formula like =CELL(“row”) will return the row number of the cell you were in when Excel recalculated last. It creates kind of a delayed effect and could conceivably not be caught at all.
Here’s a mock up of the situation. I assure that the actual formatting is far more gaudy than this.
The formulas:
B4: =COUNTA(H4:K4)<>2
C4: =CELL(“row”)<>ROW()
D4: =AND(OR(A4,B4),C4)
I’ve just entered something in F5 and the error doesn’t show because I’m still in that row. What’s your opinion of this technique?
Dick –
If you don’t have to highlight the entire row, but can do only the offending cells:
Condition for $F$4:$F$7
=AND(LEN($F4)=0,LEN($G4)>0,COUNTA($H4:$L4)>0)
Condition for $G$4:$G$7
=AND(LEN($F4)>0,LEN($G$)=0,COUNTA($H4:$L4)>0)
Conditions for $H$4:$L$7 look left and right
=COUNTA($H4:H4)>2
=COUNTA(H4:$L4)>2
…Michael
Fat finger typo above–should be LEN($G4) vice LEN($G$)
I’d be tempted to use a simple SelectionChanged event and a ‘CurrentRow’ cell instead of the formulae with =CELL(“Row”) in. The code then updated the CurrentRow cell if (a) the Target is inside the table and (b) the Target.Row is different to the value of the CurrentRow cell.
Better conditions for $H$4:$L$7:
=AND(LEN(H4)>0,COUNTA($H4:H4)>2)
=AND(LEN(H4)>0,COUNTA(H4:$L4)>2)
Interestingly, if just 3 “others” are filled in, the one in the middle
isn’t in error, the ones on the left and right are.
…Michael
Stephen: Okay, I’m going to try it. The code will have to go in my app class in the add-in. First I’ll have to make sure the target is in a workbook that’s part of the app. Also, this add-in is always running. It just seems like a ton of overhead for the few times it’s necessary.
Dick,
I have an addin that runs all the time, which runs code for just about every application-level event. I was just doing some rough testing last night and, surprisingly, all that overhead doesn’t seems to make a negligible (maybe 1%) difference. More important, I’ve never felt like it is slowing things down. I have a few users with it and they don’t seem to be negatively affected either.
First, if the record in F4:L4 were blank, then COUNTA(H4:K4) would be 0, so COUNTA(H4:K4)2 would be TRUE, indicating an error. But it wouldn’t be an error because the record is blank. So I believe the formula in B4 should be changed to
=IF(COUNTA(F4:L4),COUNTA(H4:K4)2)
Volatility can be addressed using IF. That is, given the formula
=IF(s,VolatileFunction())
If s is equivalent to TRUE, the formula is volatile, but if s is FALSE, it’s not. You can test this assertion with the udfs
Function f() As Long
Application.Volatile
Static n As Long
n = n + 1
f = n
End Function
Function g() As Long
Application.Volatile
n = n + 1
g = n
End Function
Enter 1 in A1, =IF(A1,f(),g()) in B1, =RAND() in C1, and enter in succession 1, 2, 3 in D1. Both B1 and C1 recalc on each entry, with B1 = 4 after entry of 3 in D1. Then enter 0 in A1. B1 will update to 1, the value returned by g(). Enter 1, 2, 3 in D1. Now only C1 updates and B1 remains 1. Then enter 1 in A1. B1 will update to 5. This shows that Excel didn’t evaluate f() while A1 was 0 and D1 received different entries.
So all you need to do to avoid the unnecessary evils of the volatile CELL function is to replace your D4 formula with
=IF(OR(A4,B4),C4)
which means that the CELL function in C4 would only be evaluated when necessary.
Indeed, since my proposed replacement formula for B4 effectively uses the same IF condition as A4, the formulas could be rewritten as
A4: =COUNTA(F4:L4)
B4: =IF(A4,OR(ISBLANK(G4),COUNTA(H4:K4)2))
C4: =IF(B4,CELL(“Row”)ROW())
D4:
Further, this could be rolled up into a single formula,
=IF(COUNTA(F4:L4),IF(OR(ISBLANK(G4),COUNTA(H4:K4)2),CELL(“Row”)ROW()))
The inequality operators in my last post were filtered out (treated as empty HTML tags?), so the 2s immediately following right parentheses should be right parentheses followed by inequality comparison operator followed by 2.
Also screwed up the g() udf.
Function g() As Long
Static n As Long
n = n + 1
g = n
End Function
Hi Dick,
Dunno if you can use this…
I had shared this “workaround” somewhere for the problem with data validation being lost when one pastes something into the cell with d.v.
You can leverage the same technique here. In fact, one might even argue that the workaround is more informative in that it identifies the problem.
In E4 enter the formula
=IF(COUNTA(F4:J4)=0,””,IF(ISBLANK(F4),”Missing customer”,IF(ISBLANK(G4),”Missing invoice”,IF(COUNTA(H4:J4)<>2,”Need 2 items!”,””))))
Conditional format E with red font if it is not blank using the formula =E4<>””