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.
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?