Using data from multiple workbooks to generate a report

[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.

 

[Bernard Liengme]

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

best wishes

 

[Dave Young]

No, the workbooks have names of the places where the equipment is located.

 

[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