Hi Paul

Take a look at Pivot Tables. Your data is ideally suited for analysis
with PT's
Do you really have 64000 lines of data?
I would used a Dynamic range as the source for the PT with the
following
InsertNameDefineNamed range MyData Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),6)
This range will grow automatically as you add more rows

Give the PT Mydata as the source range

For more help take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"PaulW" wrote in message
...
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