Hi Greg,

I had no problem using a worksheet as the source, providing I copied the
formula from the formula bar (F2, Ctrl-C, Enter, and then paste using Ctrl-V
as before.)

I often use this technique for complex defined names too.

---
Regards,
Norman



"Greg Wilson" wrote in message
...
Thanks a million pointing that out Norman !!!

I can't get it to work when I copy directly from a worksheet, whether
using
Ctrl-V, the right-click menu or through the Edit menu. Apparently, Excel
kills what's on the clipboard when copied from a worksheet (i.e. when in
CutCopy mode) when dialogs are opened. The same holds for the Conditional
Formatting forumula window and the named range "Refers to" window.

So, the answer appears to be to write your formulas in a code module, then
copy and paste to either of the above using Ctrl-V. After all this time I
never knew that. This will be extremely useful.

Looks like I will benefit more from this post than stephan. Please advise
if
I'm missing something. Thanks again.

Best regards,
Greg