I currently use dozens of separate workbooks that contain data and must copy
and paste the data to a summary workbook for analysis. I would like to be
able to retrieve the data from the proper workbook by specifying either part
of all of the workbook name as the column header in the summary workbook.

For example: If column A in the summary workbook is titled "(2-27-06)" I
would like cell A5 to retrieve the data contained in workbook "Material Data
(2-27-06)" cell M29.

Also, If I change the column title in the summary workbook I would like the
data retrieved from the new data workbook, e.g; (2-28-06).

I have tried several reference and lookup functions to no avail.

Hi!

No "good" way to do this. In order to reference a workbook name as a cell
reference you'd need to use the Indirect function and this would require
that the other wbs be open.

I doubt that you want "dozens of separate workbooks" to be open!

Biff

"blackreugen" wrote in message
...
I currently use dozens of separate workbooks that contain data and must
copy
and paste the data to a summary workbook for analysis. I would like to be
able to retrieve the data from the proper workbook by specifying either
part
of all of the workbook name as the column header in the summary workbook.

For example: If column A in the summary workbook is titled "(2-27-06)" I
would like cell A5 to retrieve the data contained in workbook "Material
Data
(2-27-06)" cell M29.

Also, If I change the column title in the summary workbook I would like
the
data retrieved from the new data workbook, e.g; (2-28-06).

I have tried several reference and lookup functions to no avail.