I currently have 5 Spreadsheets, one for each team. Each of these Workbooks
includes a page for each person in that team, with the addition of
"Consolidated" , "Temps" and "Unallocated". These sheets are used to monitor
every piece of work in and completed by everyone.

All the sheets in all 5 books look similar, with dates along row 1, and jobs
down column A, with totals at the bottom. Column B is filled with "Start" and
"Finish" so if someone is given 10 inputting to do and they do 4 they find
the correct date along the top, the correct job down the side and in the
start/finish boxes put 10 and 6.
The only major difference between Workbooks is that the job list is tailor
made for each team.

Several problems have arisen with these sheets since I set them up. Apart
from the obvious size issue of the files, there are only 265 columns, which
is less than a year of weekdays. Adding new staff and new jobs for a team is
a time consuming process. Plus, everytime something is moved any sheets
referencing at these archive files need to be updated (mostly due to the
totals being at the top) and a major issue is mistakes while being completed.

To solve as many of these problems as I can, I'm setting up new sheets that
have the dates down the left hand side. Using another Sheet to complete that
day's productivity (either on a team or individual level) and activating a
macro copies the information to these new sheets, and on a job by job level
calculates where the information should be stored, adding any new jobs and
dates as needed. Since only jobs and dates used are there, it saves largely
on space, these sheets have gone from 20meg to 10meg just like that.

The trouble I am having is getting totals. Other workbooks will want to
automatically pull this information to use to provide Productivity / Work /
Time levels over teams or the company over differing times.
To find productivity for a team, I need to be able to look at X amount of
sheets (all sheets apart from "Calc") find the appropriate date in Column A
for each sheet then add everything for that row, Column D, then compair that
to similar for Column E (the totals are fixed in those two columns regardless
of how many jobs are answered, learnt from that mistake)

First off I tried to have somewhere in the "Calc" sheet that used Indirects
to look at every sheet (the macro used to add a new sheet for a new person
also adds that name into row 144) but since I still don't know what dates
these other sheets will need I had to include them all in these totals. All
these indirect formulas slowed the sheet down when the "updating" process was
taking place and I'm hoping to avoid that.
The only other thing I can think of is having on every sheet that calculates
productivity a series of macros that pull the information as required, but
that seems unwieldy everytime someone wants to know what the productivity was
on a certain day. And on more complex sheets I currently have that look at
how much work was done, compairing against hours worked, and looks at a
supplied figure and gives the dates closest to that much "Work Done" with how
many people were working and predicts how many people need to work. A sheet
like that calculates it all pretty much in the blink of an eye with formulas,
but with macros I'd imagine it'd take alot longer.

So, can anyone help me with any suggestions? I'm sticking with Excel,
dispite the fact i'm sure something else would be more appropriate. If only
the Indirect formula worked on a closed workbook, then I think I could easily
pull off the information.

Cheers if you've read all this, I wanted to make sure I was being clear on
the layout. I hope I haven't made these sheets too complex, its the easiest
way to save and store the information I feel.