Cells Refresh Problems

[BMPeers]

This made one of the worst days at the office even bad, please help!

I couldn't work out why my cells are not recalcualting.
I checked Tools > Options - Calculations and this is set to automatic.

However if I click into a cell or press F2 and then Enter this seems the only way the cell will update.
A typical formula which won't update:

=IF(ISERROR(VLOOKUP($D68,'08 Feb'!$G$2:$H$200,2, FALSE)),0,VLOOKUP($D68,'08 Feb'!$G$2:$H$200,2, FALSE))

Thanks in advance for any insight.

 

[schielrn]

Try doing contol+H for find and replace. Then in find put without quotes:

"="

And then in replace put without quotes:

"="

And replace all and see if that helps. It may seem stupid, but for some reason it works.

 

[Gerald Higgins]

Hi schielrn, why might that work ?

 

[schielrn]

Not positive, but I had this problem a way long time ago and was told to do this and it worked. Not sure why. I have also seen others suggest this (Just the first one I came to):

http://www.mrexcel.com/forum/showthread.php?t=311396&highlight=calculate+replace

 

[BMPeers]

to schielrn,

thanks very much for your help. i now live in KY not England, so hello across the Ohio.

your tip indeed worked, thanks for the help. it seems however that the entire spreadsheet has broken down, nothing is linking from one sheet to another..

is this just a fatal flaw in excel which i have to accept? lucky bill gates has just retired...

thanks brian

 

[schielrn]

Is it a very large spreadsheet? Are there a lot of links accross sheets and workbooks? Not sure exactly what you mean by broken down? The tip I gave fixed the problem, but then it occurred again?

 

[BMPeers]

its 6meg... and there are a couple of matrices which are about 400 rows by 30 columns all with links...

i have had larger spreadsheets without this problem...

what i mean is by broken down that, is if you refresh using the search and replace, that works once..

but if i change the input data again then it doesnt update again.. so i will need to carry ouy the search and replace everytime i paste in the input data (which is pasted in as values)

aaaaaaaaaaaah!

 

[schielrn]

Did you try setting the cell to general possibly before pasting in the values? Maybe for some reason it is text, but importing usually causes problems depending on the source.

 

[BMPeers]

Thanyou!!

The input format was ############0.00 or something custom.
Once I changed it to a standard number it seemed to link through ok.

Very useful to know, thanks again.