how do i substitute filename in a formula

[Rupesh]

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

 

[Pete_UK]

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

 

[Rupesh]

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

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

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

 

[Pete_UK]

There is a free download available here:

http://www.download.com/Morefunc/3000-2077_4-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

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

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.

- Show quoted text -