Solved

Links between worksheets WITHIN same Excel workbook not updating automatically

Posted on 2013-11-01
6
749 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
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.
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.

746 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

12 Experts available now in Live!

Get 1:1 Help Now