Links between worksheets WITHIN same Excel workbook not updating automatically

I am using MS Office 2010, Excel.

I have a workbook that I have been using for several years, created in Office 2003 but it has seemed to be OK withing Office 2010.  It has many worksheets and links between those worksheets (but all within the same workbook).  These inter-sheet links have stopped working unless I click on the formula bar of the cell in question and hit Enter.

I have checked that Calculation is set to Automatic.  I have closed and reopened Excel.  A colleague experiences the same problem with the same workbook.  Links within a worksheet seem to update as expected, it is only links to other worksheets within the same workbook that do not work.

Is the file corrupt or is there a fix?  Please help!

Thanks

Eric
childejcAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mark_harris231Connect With a Mentor Commented:
Create a "test" copy of the current workbook.

In the test copy, create a new worksheet.  Insure that the columns are set to General format.

From the "broken" worksheet, select an entire row that contains a formula that isn't working.  Copy/paste it to the new worksheet.  If the formula works, select all cells on the "broken" worksheet, copy/paste them into the new worksheet and confirm results.

If that seems to fix the formula issue, rename the "old" worksheet (don't delete yet), and rename the new worksheet to the old sheet name.  Hide the old worksheet until you are fully confident of the copy/paste solutions.  If all seems to be in order, perform the same routine on the "live" worksheet (but make an archive copy first).
0
 
unknown_routineCommented:
This can happen if Excel changes the path of the links from absolute. Right click on a link and make sure it is valid.

See here:
 This http://www.excelbanter.com/showthread.php?t=229767.
0
 
childejcAuthor Commented:
Thanks for your prompt response.  However I'm not sure how to do what you suggest.  The links do not have a "path" as they are simply links between worksheets (within the same file).  These are 2 examples of formulas which do not update until I click on the formula bar and press enter.

=INDEX('Integration Sites'!G:G,MATCH(D230,'Integration Sites'!B:B,0))
=SUMIF('MS Site Specific Chgs'!$CE1:$CE504,BA$28,'MS Site Specific Chgs'!$CG1:$CG504)

I do not have to change the formula in any way, just press enter after clicking into the formula bar.  Once I have made the formula update once it seems to update correctly thereafter, but I can't do that cell by cell for every formula in the file (it is a very large file).  Is there way of globally forcing all formulas to "update"?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
childejcAuthor Commented:
Sorry for the delayed response and thanks for the advice.  I had some time pressure to find a work around and it seemed quicker to revert to an earlier version of the file that did not exhibit the same issues.  So I have not yet tried this proposal.  However, if the problem recurs I will.

Thanks again

Eric
0
 
mark_harris231Commented:
Understood, Eric.  Glad you were able to reach a workable conclusion.  Recommend you close this case by accepting your last post as the solution (or alternately, requesting it be deleted).
0
 
childejcAuthor Commented:
In the event, due to time pressures, it was quicker to revert to an old version of the file, so I have not actually tested this solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.