Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract value from a different file?
Q: There are 2 seperate files....File A and File B. I am trying to extract
data from file A into a cell in file B. The problem is file A's name changes according to current date e.g. 20060404_A.xls for April 4th and 20060405 for Apr 5th. I have used concatenation and it returns "string" that resembles the entry if file A's name would be constant. I am unable to code the fact that I require the "value" for that string as a function and not just the string. E.g. ='D:\My Documents\test\[20060404_A.xls]Analysis'!$C$24' returns the correct value....But with concatenation when the string returns ='D:\My Documents\test\[20060405_A.xls]Analysis'!$C$24' (for the NEXT day, check date) ... I dont get the value just text. Thanks Firenzeitl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract value from a different file?
Hi
You can use INDIRECT to return a cell reference from a string, but there is a drawback - INDIRECT works only, when the source file is opened too. There was mentioned a UDF someone created, which worked like INDIRECT, but with closed source file. But although I saved the thread then, it was lost when my HD was broken. Another way is to write a procedure, which replaces all references in given range with new ones. I.e. you enter or select the source file name (and its path) into some cell(s), and start the procedure (you can have a button on sheet for it). And all link formulas are rewritten. Arvi Laanemets "Firenzeitl" wrote in message ... Q: There are 2 seperate files....File A and File B. I am trying to extract data from file A into a cell in file B. The problem is file A's name changes according to current date e.g. 20060404_A.xls for April 4th and 20060405 for Apr 5th. I have used concatenation and it returns "string" that resembles the entry if file A's name would be constant. I am unable to code the fact that I require the "value" for that string as a function and not just the string. E.g. ='D:\My Documents\test\[20060404_A.xls]Analysis'!$C$24' returns the correct value....But with concatenation when the string returns ='D:\My Documents\test\[20060405_A.xls]Analysis'!$C$24' (for the NEXT day, check date) ... I dont get the value just text. Thanks Firenzeitl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel file automatically opens | Excel Discussion (Misc queries) | |||
Links picking up values from an older version of linked file | Links and Linking in Excel | |||
cannot open excel file, please help!!! | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) |