Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
Is there a way to short this substitute formula? | Excel Discussion (Misc queries) | |||
how do I repeatedly substitute a value in a formula | Excel Worksheet Functions | |||
substitute the filename in a cell reference with a string in another cell. | Excel Discussion (Misc queries) | |||
Substitute Formula | Excel Worksheet Functions |