Formula not evaluating immediately

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Excel uses arguments (cell references) to functions and formulas to establish the order of recalculation (the dependency tree).
The literal in the INDIRECT function call is not recognized as a cell reference, so the order of calculation may be incorrect. If
it is evaluated after other cells which influence your result, it may show the correct value only at the next recalculation.

I'm not sure what you mean by "procedure" in this case.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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 said:
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
Click to expand...
 
<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
| >
| >
| >
 
Back
Top