Here’s a funny little bug I came across today. So I’m adding a CF condition, and I push F3 to bring up Paste Names rather than type out a reference or name:
And clicking on that named range duly inserts it into my CF formula:
…but the only problem is, now I can’t seem to do anything else. I can’t type the closing bracket, because Excel won’t let me. I can’t backspace to remove the offending name, because Excel won’t let me. The cursor is still flashing, so Excel hasn’t hung.
If you type the name in manually rather than using F3, it works perfectly. It turns out its some kind of focus issue: if you push ALT + TAB the screen flickers, but you still have the Conditional Format dialog open and now it works perfectly.
It’s incredible what you can do with CF if you’ve got a good memory. The good memory bit is required because CF doesn’t let you write notes about what each condition does like Names do, or even assign those CF conditions meaningful names. Let alone (heaven forbid) resize the dialog box so you can see all your rules at once, to help you work out which does what:
With a good memory, it’s an incredibly strong and useful tool. With my memory, it’s downright mysterious.
Shame, because I’m using it to really good effect to steer users through a pretty complicated self-assessment tool. I pity the poor sap that comes after me that has to amend these rules. On the other hand, that poor sap will probably end up hiring me back at my urgent pickle rate. Thanks, Microsoft.
