Solved

Links between worksheets WITHIN same Excel workbook not updating automatically

Posted on 2013-11-01
6
771 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:childejc
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:unknown_routine
ID: 39616680
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
 

Author Comment

by:childejc
ID: 39616993
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
 
LVL 10

Accepted Solution

by:
mark_harris231 earned 500 total points
ID: 39617200
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:childejc
ID: 39629479
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
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39630155
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
 

Author Closing Comment

by:childejc
ID: 39638080
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how ViaSat reduced average response times for IT incidents from 10 minutes to 30 seconds.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now