Formulas Won't Auto Calc

[Guest]

OK, before you state the obvious, Auto Calculate is "on", I've tried Calc
Sheet, Manual & "F9", "F9" in Auto, switching between all four, closing and
reopening the file, rebooting, etc. The cell containing the formula is not
formatted as Text, the formula is fairly simple:

=IF(OR(ISERROR(VLOOKUP($B24,'Q1-06'!$A:$AH,12,FALSE)),$C24="Y"),0,VLOOKUP($B24,'Q1-06'!$A:$AH,12,FALSE))

The lookup value of $B24 originally was not it in the tab I was looking for
it (Q1-06). I revised an existing value within the Q1-06 tab to match what
was in the original $B24 Cell. The value of $C24 is not "Y" (it’s actually
“Nâ€). All values are within the established lookup range and both are
formatted the same way. After revising the Q1-06 tab to match $B24, the
cell/formula still yields a zero. I ran through every combo possible via
Auto, Manual, F9, Calc Sheet, etc to get it to acknowledge the match now
found within the formula and yet it remains zero.

However, when I hit "F2" and Enter within the formula
[=IF(OR(ISERROR(VLOOKUP($B24,'Q1-06'!, etc] cell, while changing nothing
within the cell or formula, the formula recalculates and brings back the
expected answer based on the look up finding the match in Q1-06. F2 is the
only way to get the formula to yield the expected result within all the Cells
on the tab.

Totally confused, any insight would be greatly appreciated.

Regards,
Mike

 

[Jim Cone]

Mike,
I did not try your formula, but it appears that the "OR" might be
the problem. Try removing that (and adjusting the parentheses) and
see how it works.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Mike The Newb"
<[email protected]>
wrote in message
OK, before you state the obvious, Auto Calculate is "on", I've tried Calc
Sheet, Manual & "F9", "F9" in Auto, switching between all four, closing and
reopening the file, rebooting, etc. The cell containing the formula is not
formatted as Text, the formula is fairly simple:

=IF(OR(ISERROR(VLOOKUP($B24,'Q1-06'!$A:$AH,12,FALSE)),$C24="Y"),0,VLOOKUP($B24,'Q1-06'!$A:$AH,12,FALSE))

The lookup value of $B24 originally was not it in the tab I was looking for
it (Q1-06). I revised an existing value within the Q1-06 tab to match what
was in the original $B24 Cell. The value of $C24 is not "Y" (it’s actually
“Nâ€). All values are within the established lookup range and both are
formatted the same way. After revising the Q1-06 tab to match $B24, the
cell/formula still yields a zero. I ran through every combo possible via
Auto, Manual, F9, Calc Sheet, etc to get it to acknowledge the match now
found within the formula and yet it remains zero.

However, when I hit "F2" and Enter within the formula
[=IF(OR(ISERROR(VLOOKUP($B24,'Q1-06'!, etc] cell, while changing nothing
within the cell or formula, the formula recalculates and brings back the
expected answer based on the look up finding the match in Q1-06. F2 is the
only way to get the formula to yield the expected result within all the Cells
on the tab.

Totally confused, any insight would be greatly appreciated.

Regards,
Mike