If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Links not updating automatically
Hi. I have a 7-workbook Excel spreadsheet, which contains links to several
other spreadsheets. When I open the document, I click Yes to the "Do you want to update links?" question, but the information in the linked cells is not updated. However, if I click on the cell, then click at the end of the link in the formula bar and press Return, the link updates. I've checked that the "update remote references" checkbox is marked on the Calculation tab in Tools...Options and now I'm stuck. Any help much appreciated. Thanks. Lisa |
#2
|
|||
|
|||
Links not updating automatically
Hi Lisa
Please help us with a bit more information: - which version of Excel are you using? - might the source workbook have been saved by an earlier version of Excel? [this often prevents link updates] - I assume by 7-workbook spreadsheet you mean 7-worksheet workbook (ie a single file)? - When you say the information is not updated when you open the workbook and ask for links to be updated, does a typical cell containing a link formula show an error value such as #REF! or does it just contain an out of date value? - Please can you give an example of a formula which does not update correctly? (By the way, I like your screen-name!) Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Links not updating automatically
When I open the document, I click Yes to the "Do you want to update links?" question, but the information in the linked cells is not updated. However, if I click on the cell, then click at the end of the link in the formula bar and press Return, the link updates. We have the exact same problem, and it has started recently. The version is Excel 2007 with all the latest updates applied. If we open a .xlsb file with links to other files, half of the cells with links shows zeros, and the rest show data. (pointing to the same source/link file). We have files that easily reproduce this error if someone wants to have a look at it, it's defenetly something spooky in Excel after the latest updates. /Magnus |
#4
|
|||
|
|||
Links not updating automatically
If you'd like to email me the files at Bill underscore Manville at
Compuserve dot com, I will take a look and report the problem to Microsoft if I can reproduce it. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
|
|||
|
|||
Links not updating automatically
Thanks Bill. Responses below.
- using 2003 - source workbooks are saved using 2003 also - yes, I mean a single file with 7 sheets - cells contain data from the last update - eg "=+'S:\ACCOUNTS\OPRSTMNT\[Opgroup.XLS]GRP'!A34" Hope this helps Bill, thanks a million. Lisa "Bill Manville" wrote: Hi Lisa Please help us with a bit more information: - which version of Excel are you using? - might the source workbook have been saved by an earlier version of Excel? [this often prevents link updates] - I assume by 7-workbook spreadsheet you mean 7-worksheet workbook (ie a single file)? - When you say the information is not updated when you open the workbook and ask for links to be updated, does a typical cell containing a link formula show an error value such as #REF! or does it just contain an out of date value? - Please can you give an example of a formula which does not update correctly? (By the way, I like your screen-name!) Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
|
|||
|
|||
Links not updating automatically
Alright, Flowerg.
Nothing obvious then. You definitely have access to drive S when opening the file and the source workbook exists? If you go into Edit Links, select a link and click Update Values do the values change? If not, if you open the source workbook do the values change? If not, if you Ctrl+Alt+F9 do the values change? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#7
|
|||
|
|||
Links not updating automatically
If you'd like to email me the files at Bill underscore Manville at Compuserve dot com, I will take a look and report the problem to Microsoft if I can reproduce it. Sure I will do that, I have just asked the guy here working with it to put together a step-by-step guide to reproduce the bug. In the meantime I will answer the questions you wrote to Lisa If you go into Edit Links, select a link and click Update Values do the values change? No, nothing changes, some cells with links still show zeros If not, if you open the source workbook do the values change? Yes, If I open the source workbook, the links get updated and show the values correctly If not, if you Ctrl+Alt+F9 do the values change? No, If the source workbook isn't open while I open the first workbook containing the links, some cells with links will show zeros, the only thing that seems to work is if I open the actual source workbook. It looks like we have reached some kind of memory limitation in Excel regarding how many links you can have or something, but the strange thing is that it works if the source workbook is open in Excel, otherwise it dont. I will send the files tomorrow, would be great if you could have a look at this, really appriciated. /Magnus |
#8
|
|||
|
|||
Links not updating automatically
Links will almost always work if the source workbook is open (unless
the destination workbook has a broken calculation tree - hence the Ctrl+Alt+F9 suggestion). There are a number of possible reasons for problems with links when the source workbook is closed, but we have already eliminated some of those. I will look at the workbooks when I get them. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#9
|
|||
|
|||
Links not updating automatically
"Bill Manville" wrote: Links will almost always work if the source workbook is open (unless the destination workbook has a broken calculation tree - hence the Ctrl+Alt+F9 suggestion). There are a number of possible reasons for problems with links when the source workbook is closed, but we have already eliminated some of those. I will look at the workbooks when I get them. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#10
|
|||
|
|||
Links not updating automatically
I've just come searching for an answer to exactly the same problem. I've got
a workbook with links to another workbook that do update, and to another that don't (unless I've opened it first; doesn't matter if it's subsequently closed again). I'll be well pleased if someone can answer it. |
|
Thread Tools | |
Display Modes | |
|
|