Cell displays different result than Fx

[RyanJonesCasey]

Hi,

I have a formula that sums up 3 different VLOOKUP values from data
stored on a separate tab. However, when I paste over the old data
with new data (a downloaded page from one of my company's systems),
the cell results do not change. However, when I click into the "fx"
for the formula, it displays a different result (which is correct).

I have tried entering the formula as an array, but that didn't help
when I pasted in the new data. Here's my formula.

{=SUM(IF(ISERROR(VLOOKUP(G$8,'Robert Morris'!$B$1:$Z$99,14,FALSE)),
0,VLOOKUP(G$8,'Robert Morris'!$B$1:$Z
$99,14,FALSE)),IF(ISERROR(VLOOKUP(G$7,'Robert Morris'!$B$1:$Z
$99,14,FALSE)),0,VLOOKUP(G$7,'Robert Morris'!$B$1:$Z
$99,14,FALSE)),IF(ISERROR(VLOOKUP(G$6,'Robert Morris'!$B$1:$Z
$99,14,FALSE)),0,VLOOKUP(G$6,'Robert Morris'!$B$1:$Z$99,14,FALSE)))}

Anyone have any thoughts?

Thanks!
Ryan

 

[Dave Peterson]

My first guess is that you don't have calculation set to automatic.

Tools|Options|Calculation tab

 

[RyanJonesCasey]

I double checked, and I do have it set to automatically calculate.

Not until I click into the formula bar and hit "enter" does the value
in the cell update to match what is shown in the "fx" window. Do you
have a second guess?

 

[SpreadsheetBrian]

when I paste over the old data

with new data (a downloaded page from one of my company's systems),
the cell results do not change

This could be because you do not have "Automatic Calculation" enabled.

Tools > Options > Calculation

*) If Calculation is not "Automatic", make it so.
*) If Calculation is already "Automatic", try pushing the "Calc Now
(F9)" button to see if that causes your formula to update.

Brian Herbert Withun

 

[Dave Peterson]

I don't have a guess why it occurs, but I do have a suggestion to try.

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

I've never seen this, but there have been posts that describe the same problem
as you're having. By doing this mass change, it forces excel to reevaluate
every formula. And it seems to wake up excel's calculation engine.

(Nope. No idea if this is permanent or not.)

 

[RyanJonesCasey]

I double checked, and I do have it set to automatically calculate.
Hitting F9 did not cause the formula to update.

Any other suggestions? I know, this seems strange.

Ryan

 

[SpreadsheetBrian]

It may also be a matter of where you copy from, and how you paste into
your worksheet.

Try paste-special as "Text" or "Unicode Text"

Brian Herbert Withun

 

[RyanJonesCasey]

Thank you Dave! It worked! Very strange, I don't understand why it
just doesn't work. But thanks for the workaround!

Ryan