The file links to different workbooks by conditions, I can get the file
name, path, etc,

such as:

in A1: ="c:\abc\"&lastmonth&".xls" -- c:\abc\june.xls

or A1: ="c:\abc\"&"["lastmonth&".xls]" -- c:\abc\[june.xls]

I name A1 as LastFile, how can I use it to reference a cell in that
file? like

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

I tried a few combinations but didn't work, what is the correct format?
Thank you.

You may need to use the function INDIRECT.EXT. It is not on the standard
Excel, you have to download it. Check this page for the syntaxis and check
if it fits your needs:
http://xcell05.free.fr/english/moref...direct.ext.htm

Hope this help,
Miguel.

"muster" wrote:

The file links to different workbooks by conditions, I can get the file
name, path, etc,

such as:

in A1: ="c:\abc\"&lastmonth&".xls" -- c:\abc\june.xls

or A1: ="c:\abc\"&"["lastmonth&".xls]" -- c:\abc\[june.xls]

I name A1 as LastFile, how can I use it to reference a cell in that
file? like

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

I tried a few combinations but didn't work, what is the correct format?
Thank you.

In order for this to work, the workbook must be opened. Try

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

"muster" wrote:

The file links to different workbooks by conditions, I can get the file
name, path, etc,

such as:

in A1: ="c:\abc\"&lastmonth&".xls" -- c:\abc\june.xls

or A1: ="c:\abc\"&"["lastmonth&".xls]" -- c:\abc\[june.xls]

I name A1 as LastFile, how can I use it to reference a cell in that
file? like

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

I tried a few combinations but didn't work, what is the correct format?
Thank you.

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.

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.

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.

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.

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.

Yes. Finally it worked. It must be exatly the same including single
quotes. Yes I have to use indirect() and the workbook must be open.

So I'll check indirect.ext() or write a macro to open & copy the data.

Thanks a lot.


Barb Reinhardt wrote:
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.