How to extract value from a different file?

[Guest]

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

 

[Arvi Laanemets]

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