Indirect( ) function loosing values when spreadsheets are closed

[Word4Dummies]

When using the indirect( ) function that points to cells referring to
external workbooks I get a #REF error when the external spreadsheets are
closed.

e.g. Cell A1 of workbook "testing.xls" has the following text string in it:
'test1.xls'!sheet1$A$1 , in cell A2 of "testing.xls" I have the following:
=INDIRECT(A1), the value returned is 4000 (the same as the value in A1 of
workbook test1.xls.) The problem is tha the value is only returned when
test1.xls is open, otherwise #REF is returned

Please help.

 

[Ragdyer]

Yes, it's a fact of life, Indirect() does *not* work on closed WBs.
You'll have to make direct links to the other WB, in order to return the
contents of the cell in question, such as:

=[test1.xls]Sheet1!$A$1

 

[Arvi Laanemets]

Hi

I myself use 2 different solutions, when I need to lookup data from another
workbook:

1) I create a mirror sheet, using simple links, and use this mirror sheet
as lookup source. Usually I hide the mirror sheet from user.

2) When it's enough when I get data from source only when I open the
worksheet, i.e. no real-time connection is needed, then I use ODBC query to
download all needed data into separate (hidden) worksheet, and again use
this hidden sheet as source instead of external workbook. This solution
allows limit downloaded data, when there is a need for this, and as the
query result table doesn't contain any formulas, the workbook performance is
increased. When I feel, that user may need refresh query data during
session, then I place a command button on some sheet, which starts query(es)
refreshing procedure.


Arvi Laanemets