<G3 is a workbook name like Workbook.xls>
If G3 is the result of a formula (which can evaluate to other valid workbooks as well) then the order of recalculation is
relevant. And that order may be disturbed by the literal in the INDIRECT() function.
BTW Barb, can you show your code?
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
|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
| >
| > | 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 procedure:
| > | > > >
| > | > > > =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
| >
| >
| >
