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
Last edited by drothman66; 02-10-2010 at 09:44 AM.
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")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
thanks...i've installed it and it works in the sense that i'm using indirect to access the closed workbook, but i'm not quite getting the syntax to put together the strings to form the argument to the function.
my exact formula is this:
=INDIRECT.EXT("'k:\[havedata.xlsm]Sheet1'!A1")
what i'm trying to do is put "k:\havedata.xlsm in a separate column and "A1" in a separate column and then form that string
i guess it's the quotes and or single quote that is messing me up.
could u provide an example where the filename and the cell ref are variables? thanks
oops..nvrmind..i got it...
the filename needs to stored as:
'k:\[havedata.xlsm]
and the rest flows naturally....
thanks for the help
uhoh...
i take that back.
yes it works if i store it that way, but if i simply edit that:
'k:\[havedata.xlsm]
by hitting F2 and then return, the indirect.ext becomes a reference error.
it has something to do with the single quote.
so i'm back in no man's land.
any idea? thanks again
ok...looks like formatting it as text is the key....
Don't put any quotes in cell A1:
Just enter: k:\[havedata.xlsm]
Then use formula: =INDIRECT.EXT("'"&A1&"Sheet1'!A1")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
works great. Can this be applied to named ranges? i've tried it and get the top left element of the range, but can i grab an entire range? i'd assume i'd have to preallocate the space on the destination sheet by highlighting it, and copying the formula to it, but i haven't quite gotten it right. thanks again
Can you elaborate with example?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
well, if i have a named range:FOO (a 6x8 array), could something like this work:
=INDIRECT.EXT("'"&A1&"Sheet1'!FOO)
where i'd get all of foo somehow?
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?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
that's close, but what i was really hoping to do was have the ability to do something like grabbing all of FOO with one command.
If FOO is 6x8, i was hoping that maybe i could highlight a 6x8 area on the current sheet and copy with a ctrl-shift-enter the indirect command to grab the entire contents of FOO.
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...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks