Can an Array Formula Get Data from Multiple Worksheets?

[snapfade]

Good Morning!

I have many Input workbooks, all identical except for the data values. On my Result workbook I want to extract the value at cell A1 from each of the other workbooks. The resulting values will appear starting in Column B1 through Bx.

I know this can be done in VBA and have done so in the past. My question is this: can the same result be accomplished with array formulas in the Result workbook?

Thanks in advance for your thoughts.

tom

[Moo the Dog]

Do you mean WORKSHEETS, or WORKBOOKS? You can have multiple WORKSHEETS in one WORKBOOK. Each WORKBOOK is its own file.

If you are referring to multiple sheets within the same workbook, and assuming your sheets are using default names (Sheet1, Sheet2, etc.) you could use this in B1 and fill down:

=INDIRECT("Sheet"&ROW()&"!A1")

- Moo

[snapfade]

Hey Moo,

Thanks for your input. I need to clarify. These are all separate workbooks, e.g., separate Excel files. All of the input files will be in the same folder and that is all that will be there so we can grab all xlsx files. That is how I did it in VBA. I would like to eliminate need for VBA and wondered if an array formula might suffice.

[snapfade]

Hey Moo,

Thanks for your input. I need to clarify. These are all separate workbooks, e.g., separate Excel files. All of the input files will be in the same folder and that is all that will be there so we can grab all xlsx files. That is how I did it in VBA. I would like to eliminate need for VBA and wondered if an array formula might suffice.

[Moo the Dog]

The only way I can think of doing this without VBA would be to make a list of the filenames somewhere and still use an INDIRECT formula. The biggest drawback is that all of the workbooks need to be open for INDIRECT to work/update.

For instance, if you had 10 files and you listed their names in cells A1:A10 of your summary workbook (A1=MyBook.xlsx, A2=AnotherBook.xlsx, etc.) then in B1 (or any other cell), you could enter this formula and fill it down 9 more rows to get the data from cell A1 of Sheet1 from each workbook:

=INDIRECT("'C:\Users\Vince\Documents\["&A1&"]Sheet1'!A1")

Obviously, you would need to add your own file path, instead of C:\Users\Vince\Documents\

Perhaps one of the amazing gurus here knows a better way of doing this without having the workbooks open, and without using VBA, but I don't.

Hope this helps...

- Moo

[snapfade]

Moo, you make an excellent point about the files needing to be open. The more I look at it the less I think I can get the job done without VBA. This was not a absolute-must-have but rather a desire. Unless someone else has other notions about how to avoid VBA, I shall go ahead and do it the "old" way.

Thanks ever so much for your contribution. Although we have not succeeded, I will mark this as solved.

Take care and stay warm in my home state (where we now wait patiently for the Pack's next season).

[Moo the Dog]

snap,

glad I could, well, attempt to help

I've done what you are trying to do, and found that VBA was the easiest way... lots of tips on the interwebs on how to do it.

Thanks for the feedback, as well. It is always appreciated. And of course, GO PACK GO! (next season) heh

- Moo