Using data from multiple workbooks to generate a report

D

Dave Young

How can I automate collecting information from 30+ workbooks that all have
the same format (i.e. Cell B25 in all workbooks has the same type of
information) and generating a report or another spreadsheet from the
collected information.

For example, all of my workbooks contain information about equipment at 30
different locations. I would like to query all of the workbooks and get a
list of what is contained in Excel Cell B25.
 
B

Bernard Liengme

Do the workbooks have a names that look like a series?
Like: Jan , Feb, Apr...
Or: Book1, Book2, ....

best wishes
 
B

Bernard Liengme

Make a list of these names (the names of worksheets) in a range such as
A1:A30
Then use the INDIRECT function in the form
=INDIRECT("'["&A1&".xls]Sheet1'!$B$25")

This works only when the other file is open. However, Laurent Longre has an
addin (morefunc.xll) at: http://xcell05.free.fr/ includes =indirect.ext()
that allows you to return values from closed workbooks.
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add additional information to excel form after original data entry 0
Use a master workbook to carry over changes to multiple workbooks 1
Creating a Chart on form from selected data in query. 0
Auto Merge text from multiple sources 0
Merge excel files with different column headings 1
Protected workbooks blocking transfer of data 3
summarizing data from various workbooks 4
Linking workbooks; experiencing random "#n/a" error 0

Top