That would explain my problems!

Lots of good solutions there. The first suggestioon seems the most fitting
for my purposes, since I only have 2 referenced workbooks. It would be easy
enough just to have those two open, but its a pain when you forget or open
the wrong one accidently. So, rather than mirror a whole load of sheets onto
hidden sheets in my open file, i was wondering how easy it would to open the
two referenced files automatically when I open the master file? (And close
them automatically?) Could you do this with a macro?

"Arvi Laanemets" wrote:

Hi

INDIRECT doesn't work with closed workbooks. you have to use some different
solution.

a) When the number of workbooks you are linking to is limited, then you can
mirror them on some hidden sheet. Now you refer to this/those hidden
sheet(s) instead of files (I have a project where ~20 workbooks are linked
to summary workbook in such a way).
b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or
Add-In on his site to download, which is an equivalent for INDIRECT, but
works with closed workbooks too. But when you want to design a workbook for
use by several users, all of them must have it in their computers - which
can be a problem.
c) Design your workbook to link to some fixed workbook. When you want to
swich the source workbook, you save it with this fixed name (not a good
solution, but for some occassions will do).
d) Create a procedure, which asks for workbook you want to link to, and
rewrites then all links. You can start the procedure from hot key, or from
button placed on worksheet.
e) ... I'm sure there are other possible solutions, but for start it will
do.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"rmellison" wrote in message
...
I am using a text string to reference a block of cells in another
worksheet.
The text string is in a seperate cell, and I am using this string in a
multitude of other cells using INDIRECT(TextStringCell). If the text
string
is in the form
[Workbook]Worksheet!A1:B2, for example, i have to have the referenced
workbook open for the link to work. I thought perhaps that if the string
was
in the form
C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced
workbook open. Needless to say, this doesn't work.

On other spreadsheets, where i do not call a reference indirectly, i can
happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do
not need to have the referenced workbook open. Is this problem just a
quirk
of an indirect reference, or is there a way around it? Perhaps I'm missing
some apostrophes some where....

Thanks in advance.