A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Links to files not working when source files closed.



 
 
Thread Tools Display Modes
  #1  
Old January 19th, 2004, 02:21 PM
Mal Osborne
external usenet poster
 
Posts: n/a
Default 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  
Old January 20th, 2004, 02:46 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default 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  
Old January 20th, 2004, 03:32 PM
Mal Osborne
external usenet poster
 
Posts: n/a
Default 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  
Old January 21st, 2004, 01:06 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.