I have one workbook for each week going back to January 2002. Each
workbook has 13 worksheets making up different locations. Each
worksheet has the exact same template and I need to extract data from
the same 8 cells on each template.
I am trying to build a yearly summary sheet for all the locations
picking up the 8 cells in each weekly sheet. Each workbook is named
"REPORT - Jan 05"; "REPORT - Jan 12" etc and are exacly 7 days apart.
For 2002 I have built a summary sheet which has each location on a
tab, the column heading are the 8 categories from each sheet and the
row headings are the week ending date (or file name). I have tried
using Text and Concatenate functions to be able to use indirect but it
seems as if Excel's resources are not large enough to handle looking
at so many sheets and performing the functions - it takes about 2
minutes to copy one cell.
Can anyone give me a suggestion on whether there is a better way to go
about this or if I should just do a manual entry input (last
solution).

Thanking you in advance.

Trevor