Accessing data from a closed workbook w/formula

i have an application where an inelegant (non VBA) solution will be more than adequate:

i have a series of workbooks: book1, book2, etc etc

all are residing on disk and closed.

in a new sheet, i simply want to grab a block of data (say a 4x6 array: a1:d6) from each and append them onto each other. Thus book1's block will occupy the first 4 rows, then book2's block etc etc.

So with 100 files, i'll end up with a 400x6 array of data.

I can form a formula such as='K:\[book1.xls]Sheet1'!A1 to manually grab the data, but what i'd like to do is create that programmatically. In other words, i'd like to have a column J where the first 4 rows are "book1.xls", the next "book2.xls" etc

and then form something like:
"'k:\["&"j1&"]Sheet1'!A1"

and use that to grab A1 from book1

if i can do that, i can simply copy that statement (and vary both the sheet and the row/column and grab all the data.

definitely klunky, but will work for this purpose.

any idea how to 'indirectly' grab data in such a way? thanks

You would need the Indirect function which does not work with closed workbooks...

But to get around that you can download and install a free addin from here: Morefunc.xll

and use Indirect.ext

Note: this addin is embeddable in workbook so you can share if necessary..

=INDIRECT.EXT("'k:\["&"j1&"]Sheet1'!A1")

Don't put any quotes in cell A1:

Just enter: k:\[havedata.xlsm]

Then use formula: =INDIRECT.EXT("'"&A1&"Sheet1'!A1")

Can you elaborate with example?

If you use it in certain formulas like this:

=INDEX(INDIRECT.EXT("'"&A1&"Sheet1'!FOO"),2,2)

then it should work to pull such things as element in 2nd row and 2nd column of FOO..

Is that what you mean?

If you select the destination 6*8 area, and then enter in the formula bar at the top:

=INDIRECT.EXT("'"&A1&"Sheet1'!FOO")

and confirm with CTRL+SHIFT+ENTER.. it should grab the whole range...