Since you have identical formulas that work,
My second guess is that fomulatext is causing an error when used in this
cell with that argument. That argument appears to be going to another
workbook. I assume that workbook is open. Are the working formulas also
going to that other workbook.

If all else fails, I would suggest using an event to fire code to reenter
the formula. Perhaps the calculate event.


--
Regards,
Tom Ogilvy



"Barb Reinhardt" wrote:

Basically what I'm doing is programmatically opening the workbook identified
in G3 and am checking to see if the formula in a specific cell contains the
value in Z1 of the existing worksheet. I've used a formula similar to this
in 9 other places without a problem, but this one doesn't work for some
reason until I manually press enter on the cell. That's a problem because
I'm opening up to 80 workbooks programmatically and am checking for the same
thing and the workbooks are located on a server across the country from me.
Do you have any suggestions on how to proceed if this method doesn't work?

"Tom Ogilvy" wrote:

Since your range reference is variable, I would guess that Excel doesn't know
when to recalculate it.

=IF(ISNUMBER(SEARCH(Z$1,FORMULATEXT(INDIRECT("'["&$G3&"]Summary
(A)'!$I$11",TRUE)),1)),"YES","NO")&Trim(Left(" "&rand(),1))

would make it volatile.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have the following formula in my procedu

=IF(ISNUMBER(SEARCH(Z$1,FORMULATEXT(INDIRECT("'["&$G3&"]Summary
(A)'!$I$11",TRUE)),1)),"YES","NO")

G3 is a workbook name like Workbook.xls. When the procedure executes, the
result is displayed as "NO" in the sheet. If I then go back and press ENTER
on the field, it changes to YES. The procedure has a CALCULATE statement in
it just in case I've got manual calculation set on my system. I actually
have it set to automatic right now. I have a similar formula in other cells
that works without a problem. I'm not sure what the problem is.

Can someone assist?

Thanks