CF meets Paste Names, CF freaks out.

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:
 
CF1
 
 
And clicking on that named range duly inserts it into my CF formula:
 
CF2
 
 
…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.
CF2

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:
 
CF dialog
 
 
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.

2 thoughts on “CF meets Paste Names, CF freaks out.

  1. Just tried to replicate this (Excel 2013) but I could successfully insert a name without the issues you describe…

    I agree with your other comments about the conditional formatting manager, especially being able to resize the damn thing!

Leave a Reply

Your email address will not be published. Required fields are marked *