Dean:

I guess I wasn't very clear. The cell computes to a number and that
number appears in, say, two decimal places on the spreadsheet,
subject to the two-decimal place formatting of that cell. Suppose I
was just curious what the value was shown to the maximum number of
decimal places EXCEL actually uses. Then I might go to the cell, hit
the F2 key, then the F9 key. Then once I copied down the result on a
piece of paper, I would hit the escape key so the formula doesn't get
wiped out. This is what I have done. When I hit escape, the formula
and value is, of course, still there in the cell.
What I am saying is that, after hitting F9 (or cntl-alt-F9, which
I've been told does a more complete recalc), the value that appears
up in the formula bar area is #NAME?.


Aha, I get it now.

Ok, cell A1 in my example below is really a very complex formula, not
something simple. But that shouldn't matter - should it? I just
fear that I cannot trust a worksheet that exhibits this kind of
behavior.

At this point, I don't know if it should matter. There's obviously
something going on in your formula that's causing this. It's not something
I've heard of before, but I think it will be fairly easy to track down. If
we can reproduce it, we may learn something. Unfortunately that "something"
may be a bug, which won't do much for our trust factor. If we can't
reproduce it, I'll end up blaming it on workbook corruption, because that's
what I do when I can't reproduce it.

First, I'll make a prediction or two. Your formula is some unreasonably
long formula and Excel can only calculate up to some limit. So you have
more than 1,024 characters in your formula, but Excel can't calculate beyond
that limit and it ends up splitting some function name in half which it then
can't recognize. This is purely speculation, so believe there's any truth
to it. Yet.

Or, you're using, in your formula, a function from a COM Add-in and Excel
can't calculate that function name when the cell's in edit mode and
therefore thinks it's an undefined range name. Again, pure guess.

Here's how you start to narrow it down. When you hit F9, it calcs the whole
formula. If you were to select a portion of the formula and hit F9, it
would only calculate that portion. The portion that you highlight has to be
valid - the parentheses have to match, for instance. So start highlighting
small portions of your formula and F9'ing them (I use Cntl+=, but that's
just a preference). If you get to a portion that returns #NAME?, you've
narrowed it down and you can narrow it down further. If you end up calcing
the whole formula without getting the error, then my first prediciton starts
to look good.

If you can share the formula, that would be best. If not, just start F9ing
small portions of it and see what you can see.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com