This is what I tried initially. I need to use Indirects because the formulas
need to include sheets that don't yet exist so they're up to date in 6/12
months. And because on each individual sheet only includes the dates used (so
if Jake were off for 2 weeks there isn't a 2 week gap, it goes straight from
01/12/06 to 18/12/06 on the next line) then I need to do a match for every
date with these indirect formulas. Once i've found the line on each sheet
that corresponds to that date (if its there) then I need another indirect to
look at that row, column 4.

=MATCH($C15,INDIRECT(CONCATENATE(ADDRESS(1,1,,,D$1 4),":A6000")),0)

40 times (to allow for more people to be added to row 14) followed by

=IF(ISERROR(D15),0,INDIRECT(ADDRESS(D15,4,,,D$14)) )

Then add those 40 together, do it again for column 5.

so thats 120 formulas (to allow for 40 people in total to work in that team
forever) for each day. Once I pull those formulas down 600/700 rows to allow
for a couple of years its 84000 formulas. And at this point the sheet is
getting slow to use, which is damned annoying when you're using a macro to
copy information about on a regular basis.

Cheers for the suggestion, as I said, its what I thought of first.

"Sean Timmons" wrote:

If I understand correctly, you are basically creating a formula that sums
from each workbook. Perhaps if, instead, you were to create a worksheet on
your master workbook that pulls the column D data only, then run your
formulas off the master spreadsheet data. (This way, your formula itself is
running off of local data). Hope that makes sense. It sounded good in my head.