External link only works for numbers -- for text values, #N/A is displayed unless linked file is open
 

Dear all,

I use external links to another excel file. In a formula with such a lin=
k, =

external links only work for numeric values, but not for text. If a cell=
=

with a text is linked, the value #N/A is displayed unless the linked fil=
e =

is open.

For instance, it happens when linking to cells of the file
http://www.eex.de/info_center/downlo...erivatives_hi=
storie_2005.xls

The formula
=3D'http://www.eex.de/info_center/downloads/dl_futures/[energy_derivativ=
es_historie_2005.xls]F1BM'!D3
is properly evaluated. Probably, because there is a number in the cell.

But, the formula
=3D'http://www.eex.de/info_center/downloads/dl_futures/[energy_derivativ=
es_historie_2005.xls]F1BM'!B3
is only properly evaluated, if the externally linked file is open. If th=
e =

linked file is not open, #N/A is displayed.

The problem occurs with Excel 97 and Excel 2000. The text content in the=
=

cells is shorter than 255 characters (usually 5-15 characters). Thus, I =
=

don't think that the 255-characters DDE limitation is the cause for the =
=

problem.

Any ideas are appreciated. Thanks in advance.

Best regards,
Sven
--
Sven Filter,

I can confirm that I am seeing the same behaviour in Excel 2002 with your file.
I saved a copy locally and the link updated fine on opening the master file.

So it seems to be a problem specific to the use of the web to get the value.
I have so far failed to find any references to this behaviour in the knowledge base etc.
I will investigate further when I have more time

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup