Excel Dynamic Formula Building

[twifosp]

I have a group of files I want to extract specific cells from and place into a single spreadsheet.

For example one of the files name is 20071022.csv.

So I can put the following =20071022.csv!$A$3 into a cell and it will give me the value for that cell.

The catch is I have a ton of these files and would like to do this automatically. So want I want to do is put the file name, which I already have in a list into column A.

20071022
20071029
ect ect

And then in the B column, build something like:

=A1&.csv!$a$3
=A2&.csv!$a$3

Obviously the above does not work. The idea is to take the text contents of A1 and combine them to the formula of b1 so I can process all of these files at once.

I know I can string text together by using ""&"" but I can't seem to get that to work in a formula, only cells that display and group text.

Any ideas?

 

[SkipVought]

Hi,

Check out the INDIRECT function.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[twifosp]

Hi Skip, thank you for the quick reply. I have not heard of the indirect function until now, thank you.

 

[twifosp]

Oops, ok this might not work after all. The help file states:

MORE INFORMATION
The INDIRECT function will only return the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed (not open in memory) the function will return a #REF! error.

The idea with this project is to avoid having to open thousands of files.

Any other ideas?

 

[SkipVought]

Guess what! Your REFERENCE will only update when the link source is open, INDIRECT or NOT!!!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}