Can references to cells be used as part of a workbook reference

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" <blackreugen@discussions.microsoft.com> wrote in message
news:5A5C596E-C41D-45CD-A918-0144F2C4A7D4@microsoft.com...
>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.
>