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 to files not working when source files closed.
I have a customer with a set of rather convoluted mish-mash of spreadsheets,
basically running an entire company. One spreadsheet (SSa) has a number of links to 4 others (SSb,c,d). Everything works as expected if SSb,c,d are open, & then SSa is opened, but if SSa is opened before SSb,c & d, cells in SSa display #VALUE!. I have tried the same set of files on several machines, running Excel 2000,XP & 2003, all act in the same manner. I have tried to recreate the problem with a set of files linked in a similar way, but in any case, the links were able to access data in closed files without problem. Any pointers on how to troubleshoot this problem would be greatly appreciated! Mal Osborne MCSE MVP (not excel) Mensa |
#2
|
|||
|
|||
Links to files not working when source files closed.
Mal Osborne wrote:
Any pointers on how to troubleshoot this problem would be greatly appreciated! Some formulas are a bit too much of a challenge for Excel to do when the source workbook is closed. The INDIRECT worksheet function will never work; some other formulas seem to depend on the Excel version. I think it was the original release of Excel 2000 that was particularly bad. Worth ensuring you have the latest service release just in case. Otherwise the answer is to have the other files open. You could produce a macro to open them. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Links to files not working when source files closed.
"Bill Manville" wrote in message ... Mal Osborne wrote: Any pointers on how to troubleshoot this problem would be greatly appreciated! Some formulas are a bit too much of a challenge for Excel to do when the source workbook is closed. The INDIRECT worksheet function will never work; some other formulas seem to depend on the Excel version. I think it was the original release of Excel 2000 that was particularly bad. Worth ensuring you have the latest service release just in case. Otherwise the answer is to have the other files open. You could produce a macro to open them. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup Thanks! OK, does seem like there is a "feral" function triping up Excel, but it is not INDIRECT. Would be real fantastic if you could post a list of likely suspects.. Mal |
#4
|
|||
|
|||
Links to files not working when source files closed.
Mal Osborne wrote:
Would be real fantastic if you could post a list of likely suspects.. I seem to remember people mentioning COUNTIF, SUMIF, VLOOKUP (and its relatives) especially if working on large ranges. If you think about what Excel has to do when calculating links to closed workbooks, it virtually has to open the source workbook in order to locate all the relevant cells and then process their contents. I guess I have some sympathy for it deciding that some are just too difficultgrin. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Thread Tools | |
Display Modes | |
|
|