how do i substitute filename in a formula
 

I need to write the file name in lets say A1 & then
refer a1 at various place to read data from that file

like a1 = c:\test\a.xls & then

in b1 i will = &a1&sheetname!a1

i am unable to do the same pls help

how do i substitute filename in a formula
 

You would normally use the INDIRECT function to do things like this.
However, INDIRECT does not work with closed files, so your file would
have to be open at the same time. If you can arrange for this to
happen, then you can use a formula like this:

=INDIRECT("'["&A1&"]"&A2&"'!A1")

with A1 containing filename.xls (no need for the path if the file is
open), and A2 contains sheet name.

Hope this helps.

Pete

On Jul 2, 11:46*pm, Rupesh wrote:
I need to write the file name in lets say A1 & then
refer a1 at various place to read data from that file

like a1 = c:\test\a.xls & then

in b1 i will = &a1&sheetname!a1

i am unable to do the same pls help

how do i substitute filename in a formula
 

What if i can not have the files open.
I just want to add list of files & then retive information from the files
names added in the sheet

Thanks



"Pete_UK" wrote:

You would normally use the INDIRECT function to do things like this.
However, INDIRECT does not work with closed files, so your file would
have to be open at the same time. If you can arrange for this to
happen, then you can use a formula like this:

=INDIRECT("'["&A1&"]"&A2&"'!A1")

with A1 containing filename.xls (no need for the path if the file is
open), and A2 contains sheet name.

Hope this helps.

Pete

On Jul 2, 11:46 pm, Rupesh wrote:
I need to write the file name in lets say A1 & then
refer a1 at various place to read data from that file

like a1 = c:\test\a.xls & then

in b1 i will = &a1&sheetname!a1

i am unable to do the same pls help

how do i substitute filename in a formula
 

There is a free download available he

http://www.download.com/Morefunc/300...-10423159.html

Morefunc gives you many new functions, one of which is INDIRECT.EXT
which is designed to work on closed files. I haven't tried it so I
can't comment on it, but it might be worth a go for you.

Hope this helps.

Pete

On Jul 3, 12:52*am, Rupesh wrote:
What if i *can not have the files open.
I just want to add list of files & then retive information from the files
names *added in the sheet

Thanks



"Pete_UK" wrote:
You would normally use the INDIRECT function to do things like this.
However, INDIRECT does not work with closed files, so your file would
have to be open at the same time. If you can arrange for this to
happen, then you can use a formula like this:

=INDIRECT("'["&A1&"]"&A2&"'!A1")

with A1 containing filename.xls (no need for the path if the file is
open), and A2 contains sheet name.

Hope this helps.

Pete

On Jul 2, 11:46 pm, Rupesh wrote:
I need to write the file name in lets say A1 & then
refer a1 at various place to read data from that file

like a1 = c:\test\a.xls & then

in b1 i will = &a1&sheetname!a1

i am unable to do the same pls help- Hide quoted text -

- Show quoted text -