Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 "Barb Reinhardt" wrote in message ... |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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not evaluating immediately
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluating a DDE formula created by strings | Excel Worksheet Functions | |||
Evaluating formula in VBA | New Users to Excel | |||
Evaluating more than 30 conditions using a formula | Excel Worksheet Functions | |||
evaluating text as if it were a formula | Excel Discussion (Misc queries) | |||
If formula evaluating 2 cells contents | Excel Worksheet Functions |