Are you saying that you want to have a formula that constructs the filepath
from other information you have. What you'll need to do is something like
this:

="'c:\abc\" & A1 & "\[sheet 1]'!A1"

You'll probably need to use the indirect function to get it to find what you
want. Let's say this is in B1, in C1 you'll need INDIRECT(B1).

This all assumes that the workbook is open.

HTH,
Barb Reinhardt



"muster" wrote:

I might have made it complicated or I didn't get it. Let me try to
rephrase my question. INDIRECT.EXT is good but I don't want to use
addins because they need maintainence.

I have got the file name (c:\abc\june.xls") for this month and save it
in a cell, A1, which is named "LastFile".

How can I use "LastFile" or A1 to construct the format
'c:\abc\june.xls\[sheet 1]'!A1, so that when it is opened next time, it
will update from june.xls? (don't need to get value right now).

I thought it's a syntax question, am I wrong?

Thank you.

Barb Reinhardt wrote:
Alternatively, you could use INDIRECT.EXT within the MOREFUNC addin from he

http://xcell05.free.fr/english/index...func_Functions

"muster" wrote:

Thank you all.

My intention at first is getting data from a file that is not opened. I
realised that is probably not feasible. I have to write a function to
do that.

But suppose the file is opened. Now I get my file name in a cell, say
A1, which named "LastFile". What is the correct syntax to reference a
cell in "LastFile"? I can't use explicit file name, because it will
change.

Harlan Grove wrote:
Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1
...

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.