how do i substitute filename in a formula

  • Thread starter Thread starter Rupesh
  • Start date Start date
R

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
 
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
 
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 said:
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
Click to expand...
Click to expand...
 
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



Pete_UK said:
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")
Click to expand...

with A1 containing filename.xls (no need for the path if the file is
open), and A2 contains sheet name.
Click to expand...
Hope this helps.
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top