Hi, i've recently looked into changing how our productivity is recorded, and
am in the process of producing all the needed sheets.

The new sheets are all in a list format, with column headers for Date,
Section, Name, Job, Start, Finish.

I've made another sheet that tries to pull off some of the productivity. I'm
currently using Sumproducts. For the daily section I have 6 cells with
sumproducts for each day, each one checks the date, section name, and 3 count
the start, and 3 count the finish. Each of these Sumproducts extend from line
2 to 64000 to get all the productivity as its entered.

This means that for 1 day that 64000 lines * 3 arrays * 6 cells = 1.1million
lines checked.
For the entire week, this is 5.5 million (no weekend work)

For the weekly section its again checking on 5 lines, but this time the
weeks of the month rather than days of the week. This time each sumproduct
includes 4 arrays, since the date needs to be done twice, once with < the end
of the week, and another the beginning of the week.

64000 lines * 4 arrays * 6 cells * 5 weeks = 7.7 million lines checked.

The months section only shows the last 3 previous months, and is similar to
the weekly just with a wider gap between dates.

64000 lines * 4 arrays * 6 cells * 3 months = 4.6 million lines checked.

This means this spreadsheet is checking around 17.8 million cells of data
each time it calculates.

When I open the sheet I get the message that Excel can't complete the task
with the available resources, telling me to choose less data or close other
applications. Since the only other application I work with open is Outlook
that option isn't too helpful. Also, since there isn't enough memory to work
it all out all the calculations come up as #REF!.

The fix I have at the moment is to set the spreadsheet so it *doesn't*
update when opened, and doesn't autocalculate. Then there is a macro in the
workbook_open that opens the linked sheet, calculates, then closes it. This
means that I only get the error about resources once (when the linked sheet
is closed)

Can anyone suggest something better than a sumproduct for this? If it were
on the same sheet I'd do Match's to find the correct dates, and indirects
using the matchs to do other matchs, so I could find the first and last
instances of that date. Then in the sumproducts i'd put indirects so that
instead of checking 64000 lines it may only be checking 200. But since its
another spreadsheet I can't use indirects for this.

Cheers