Formulas Won't Auto Calc

[Mike The Newb]

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

 

[Dave Peterson]

I'd try:

Select all the cells
edit|replace
what: = (equal sign)
with: =
replace all

Do it for all the sheets.

Maybe it'll wake up excel's calculation engine???

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