I guess you are talking about the I11. but it doesn't sound to me like she
is looking for a change in I11 in the other workbook to cause a calculate.

In any event, my first suggestion should cause the function to be evaluated
on any calculate.

--
Regards,
Tom Ogilvy


"Niek Otten" wrote:

I think the problem is the literal in the INDIRECT() function; it doesn't generate a dependency. If you change it into a reference
to a cell which contains the address, I'm pretty sure it will be cured.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tom Ogilvy" wrote in message ...
| 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