Updating Formulas when opening Workbook in 2007

[sammyf0818]

Hi,

I have a bunch of forumlas set to pull from another workbook on a shared server to help forcast The formula is: ='Q:\Folder\subfolder\subfolder\subfolder\[NPO - February.xlsx]sheet'!B2 but the formulas do not refresh when I open the current workbook. Is this possible? I have never used MACROs but when I use to use 2003 it asked me to update formulas when I opened the spreadsheet which refreshed the data. Does anyone know how ot help me? I have seen a couple of posts but nothing specifically calling this fix out.

 

[Firefly2012]

With this workbook open, if you go File ribbon/Office button>Options/Excel Options and hit the Advanced category and scroll down the list until you come to the "When calculatin this workbook" section, do you have "Update Links to other documents" checked?

 

[sammyf0818]

Yes, and Save external link values is also checked

Not sure if this matters, but:

Under General Tab:
Ask to update automatic links is also checked

 

[Firefly2012]

If you open this workbook up and use Ctrl+Shift+Alt+F9 does it force the formulas to recalc?

 

[sammyf0818]

Nope it does not update the formulas

 

[Firefly2012]

I'm sure I've experienced a similar problem to this before - I think in the end I selected the whole sheet (ie all the cells using the button to the left of the column A header) and did a Find/Replace (Ctrl+h) with Find of = and Replace of = and Lookin: Formulas and hit Replace All. This was the only way I could force the formulas to calculate. See if that works for you.

 

[sammyf0818]

It does work and update the spreadsheet with the Find and replace; but you never figured out a long-term fix? I guess the longterm fix is to open and Find/replace? seems like something is broken, well maybe just excel 2007

 

[Firefly2012]

I ended up just using Find/Replace cos it worked even if it wasn't ideal

I suspect that the problem may point to something 'broken' the workbook - you might therefore consider rebuilding it (effectively from scratch with a blank new workbook) and creating your formulas anew. They may well then update on opening as they are meant to.