Solved

Links between worksheets WITHIN same Excel workbook not updating automatically

Posted on 2013-11-01
6
824 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 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