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

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

Have you tried a pivot table?

When you say 64000 rows, is that because you have 64000 days of data or
because you are being cautious? If the latter, try dynamic ranges.

Finally, if you stick with formulae, you have to break down the formulae so
that they are not all dependent upon all of the cells. You should create
helper columns that do some of the work for you, and then check the
intermediates in your summing.

BTW, why do you say that you can't use INDIREC T on different sheets? I am
not recommending it in your situation, it is volatile and inefficient,
hardly what it sounds that you need, buy you can reference other sheets.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"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

I have avoided Pivot Tables in the past as they don't seem to update
automatically, and with their changing sizes I found them more difficult to
use than simply using Sumproducts to get everything.

The sheet currently has around 12000 lines, which is the previous years
work, but of course this will be added to each day. Might try the dynamic
ranges, but the Pivot Table seems to have sorted it all anyway.

I'm trying to avoid a helper column on the "Storage" sheet, so the sheet is
quick and easy to use regardless of how full it gets, so have put no macro's
/ formulas on it at all.

The other sheet i'm working on is another workbook, sorry I wasn't clear.

Thanks to both of you, a Pivot Table seems to be just about exactly what I
need, I suppose at a push I can just have a macro in the workbook_open that
refeshes the table. I know i've avoided that sort of thing in the past (since
half the office have macro security to high, and the other half have it on
medium and phone me up to ask if they want to enable macro's or not) but the
solution I already had in place was doing it anyway.

Thanks again.

"Bob Phillips" wrote:

Have you tried a pivot table?

When you say 64000 rows, is that because you have 64000 days of data or
because you are being cautious? If the latter, try dynamic ranges.

Finally, if you stick with formulae, you have to break down the formulae so
that they are not all dependent upon all of the cells. You should create
helper columns that do some of the work for you, and then check the
intermediates in your summing.

BTW, why do you say that you can't use INDIREC T on different sheets? I am
not recommending it in your situation, it is volatile and inefficient,
hardly what it sounds that you need, buy you can reference other sheets.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"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

Hi Paul

Rather than the Workbook open event, I would put in on the Sheet with
the PT, so it updated every time the Sheet was activated. Then if data
is added to the source, the PT would reflect that when next viewed.

Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub

(thanks to Debra Dalgleish for that tip)

--
Regards

Roger Govier


"PaulW" wrote in message
...
I have avoided Pivot Tables in the past as they don't seem to update
automatically, and with their changing sizes I found them more
difficult to
use than simply using Sumproducts to get everything.

The sheet currently has around 12000 lines, which is the previous
years
work, but of course this will be added to each day. Might try the
dynamic
ranges, but the Pivot Table seems to have sorted it all anyway.

I'm trying to avoid a helper column on the "Storage" sheet, so the
sheet is
quick and easy to use regardless of how full it gets, so have put no
macro's
/ formulas on it at all.

The other sheet i'm working on is another workbook, sorry I wasn't
clear.

Thanks to both of you, a Pivot Table seems to be just about exactly
what I
need, I suppose at a push I can just have a macro in the workbook_open
that
refeshes the table. I know i've avoided that sort of thing in the past
(since
half the office have macro security to high, and the other half have
it on
medium and phone me up to ask if they want to enable macro's or not)
but the
solution I already had in place was doing it anyway.

Thanks again.

"Bob Phillips" wrote:

Have you tried a pivot table?

When you say 64000 rows, is that because you have 64000 days of data
or
because you are being cautious? If the latter, try dynamic ranges.

Finally, if you stick with formulae, you have to break down the
formulae so
that they are not all dependent upon all of the cells. You should
create
helper columns that do some of the work for you, and then check the
intermediates in your summing.

BTW, why do you say that you can't use INDIREC T on different sheets?
I am
not recommending it in your situation, it is volatile and
inefficient,
hardly what it sounds that you need, buy you can reference other
sheets.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"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