Solved

Links between worksheets WITHIN same Excel workbook not updating automatically

Posted on 2013-11-01
6
841 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Learn how ViaSat reduced average response times for IT incidents from 10 minutes to 30 seconds.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses

623 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