| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting value from an unopened file (indirectly referenced)
1. I am working on file F1 and Cell A1 contains a file name (fname).
2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
hi,
Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Thanks FSt1.
I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
thank Dave
FSt1 "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Dear Dave
Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting value from an unopened file (indirectly referenced)
Harlan does very nice work.
learnexcel wrote: Dear Dave Wonderful ! It works. Many thanks to you and Harlan. Regards learnexcel "Dave Peterson" wrote: You'd want to use the =indirect() worksheet function. But that doesn't work with closed files. Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ learnexcel wrote: Thanks FSt1. I tried it but does not exactly solve my problem, which runs as follows (formatted as per your suggestion): Cell B2 in currebtly openbook, contains ="('D:\Something\[" & A2 & "]Details'!$B$9") FYI: Cell A2 (which is a <<Pointer to a file contains "Something-new.xls and a sheet named Details) Regards learnexcel "FSt1" wrote: hi, Here is a formula that i use to do that. =('S:\PUBLIC\Ae-pc\WOR\fy05\[WORJun05.xls]WCon'!$AS$2) you can adjust it to fit your data. it uses file path in the formula. note the single quotes around the path up to sheet number and the file name in brackets. Regards FSt1 "learnexcel" wrote: 1. I am working on file F1 and Cell A1 contains a file name (fname). 2. I want to extract the contents of cell B2 on fname (which has not been opened!)and put it onto cell B2 of file F1. How do I do this ? - I used concatenate to evaluate '<fname but it does not work - I used Indirect function, but it works only with opened files Regards -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Want blank cell displayed if indirectly referenced value is zero | Excel Worksheet Functions | |||
| Excel - How to indirectly access a file whose name is in a cell | Excel Worksheet Functions | |||
| copying data from an unopened file programmatically | Excel Discussion (Misc queries) | |||
| Extracting Footer information from unopened XLS file. | Excel Programming | |||
| download .csv file indirectly from website - long | Excel Programming | |||
Hybrid Mode
