Indirect not the correct solution???
 

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

Indirect not the correct solution???
 

676 Total Entries

52 weeks-workbooks

13 sheets each workbook = Locations

8 entries Categories- summary columns

?????- Then 13 sheets one for each location?


Week End Category 1 Category 2 ... Category 8
This is one sheet, which is a location?

Just trying to clarify
Thanks,
-----Original Message-----
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
.