I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:

=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)

On the completed workbook this turns into:

=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)

This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet). But when I create this exact same formula using
..NET I get a #NAME? error!

There is nothing wrong with the text in the cell. Forcing the
workbook to calculate using F9 doesn't help. But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works! (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).

I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on. I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas. Could that be what's
causing the problem in this case, when the formulas involve another
worksheet? And if not that, then what might it be?
--
John Brock