Slow Calculations

Maybe using a pivottable will be quicker.

mikxtr wrote:
>
> I have a file that is relying upon "sumif" statements to sum a plethora of
> data. Ok, it may not have too many rows, but rows X columns = slow file. At
> least, that's what I'm thinking. Does anyone have any suggestions on how to
> make excel more computationally efficient?
>
> Many thanks,
> Mickey
>
> P.S. I'd move it to Access, but then the powers-that-be will never read the
> report.

--

Dave Peterson

Maybe. But I need the report dynamic enough to generate different subtotals
based on input from the user. Can a pivot table recalculate itself on the
fly (e.g.
without someone specifically recalculating the table)? If so, how?

Thanks,
Mickey




"Dave Peterson" wrote:

> Maybe using a pivottable will be quicker.
>
> mikxtr wrote:
> >
> > I have a file that is relying upon "sumif" statements to sum a plethora of
> > data. Ok, it may not have too many rows, but rows X columns = slow file. At
> > least, that's what I'm thinking. Does anyone have any suggestions on how to
> > make excel more computationally efficient?
> >
> > Many thanks,
> > Mickey
> >
> > P.S. I'd move it to Access, but then the powers-that-be will never read the
> > report.
>
> --
>
> Dave Peterson
>

Nope.

But pivottables aren't made to, either. If your data that creates the
pivottable is quite large, then refreshing the pivottable could be slow, too.

So you'd be just exchanging one slow tool for a different slow tool.

Some options...
Teach the users to click anywhere on the pivottable and hit the refresh icon (!)
on that pivottable toolbar.

If you want to refresh the pivottables on a given sheet (dedicated to the
pivottable), you could add some code to the worksheet_activate event to update
just those pivottables.

Option Explicit
Private Sub Worksheet_Activate()
Dim myPT As PivotTable
Application.EnableEvents = False
For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT
Application.EnableEvents = True
End Sub

There are other things you could do, too. You may want to post a little more
info to make any response relevant.

mikxtr wrote:
>
> Maybe. But I need the report dynamic enough to generate different subtotals
> based on input from the user. Can a pivot table recalculate itself on the
> fly (e.g.
> without someone specifically recalculating the table)? If so, how?
>
> Thanks,
> Mickey
>
> "Dave Peterson" wrote:
>
> > Maybe using a pivottable will be quicker.
> >
> > mikxtr wrote:
> > >
> > > I have a file that is relying upon "sumif" statements to sum a plethora of
> > > data. Ok, it may not have too many rows, but rows X columns = slow file. At
> > > least, that's what I'm thinking. Does anyone have any suggestions on how to
> > > make excel more computationally efficient?
> > >
> > > Many thanks,
> > > Mickey
> > >
> > > P.S. I'd move it to Access, but then the powers-that-be will never read the
> > > report.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Well, here's the catch: no macros. What I'd prefer to do is store the data
in an
access table and pull only the necessary data through macros. But I've been
restricted by my boss from macros (we're trying to minimize user-error),
so there it is.

My file generates 3 reports, each on a separate worksheet. The data is 68
columns wide & 8000 rows deep (and growing). I inserted a couple of columns
to run off the user menu. In other words. the user selects the criteria
that s/he
wants to see, and these columns look at the codes in the data and return info
that determines whether that row of data is something that the report should
include. Then I sumif based on those columns.

So far, we've decided to make the calculations a little more linear.
Instead of
3 sheets calculating off the data sheet, I now have one sheet calculating
off the data sheet and the other two sheets calculating off the first sheet.

Does this help? Does it even make sense?

Many thanks for your previous suggestions.

Mickey




"Dave Peterson" wrote:

> Nope.
>
> But pivottables aren't made to, either. If your data that creates the
> pivottable is quite large, then refreshing the pivottable could be slow, too.
>
> So you'd be just exchanging one slow tool for a different slow tool.
>
> Some options...
> Teach the users to click anywhere on the pivottable and hit the refresh icon (!)
> on that pivottable toolbar.
>
> If you want to refresh the pivottables on a given sheet (dedicated to the
> pivottable), you could add some code to the worksheet_activate event to update
> just those pivottables.
>
> Option Explicit
> Private Sub Worksheet_Activate()
> Dim myPT As PivotTable
> Application.EnableEvents = False
> For Each myPT In Me.PivotTables
> myPT.RefreshTable
> Next myPT
> Application.EnableEvents = True
> End Sub
>
> There are other things you could do, too. You may want to post a little more
> info to make any response relevant.
>
> mikxtr wrote:
> >
> > Maybe. But I need the report dynamic enough to generate different subtotals
> > based on input from the user. Can a pivot table recalculate itself on the
> > fly (e.g.
> > without someone specifically recalculating the table)? If so, how?
> >
> > Thanks,
> > Mickey
> >
> > "Dave Peterson" wrote:
> >
> > > Maybe using a pivottable will be quicker.
> > >
> > > mikxtr wrote:
> > > >
> > > > I have a file that is relying upon "sumif" statements to sum a plethora of
> > > > data. Ok, it may not have too many rows, but rows X columns = slow file. At
> > > > least, that's what I'm thinking. Does anyone have any suggestions on how to
> > > > make excel more computationally efficient?
> > > >
> > > > Many thanks,
> > > > Mickey
> > > >
> > > > P.S. I'd move it to Access, but then the powers-that-be will never read the
> > > > report.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

You may want to visit Charles Williams' web site:
http://www.DecisionModels.com/calcsecrets.htm

He has a few tips that may help.

But you may want to talk to the boss once more. If you can make your program
work via code and Access, then you might be reducing the number of user errors
(although the number of developer errors could go up <vbg>).

But if your code can eliminate much of the grunt work, I would think that the
final results would be more, er, trustworthy.

Remind him or her that a computer is supposed to make those mundane tasks that
can be mechanized much more consistent (and probably lots faster!).



mikxtr wrote:
>
> Well, here's the catch: no macros. What I'd prefer to do is store the data
> in an
> access table and pull only the necessary data through macros. But I've been
> restricted by my boss from macros (we're trying to minimize user-error),
> so there it is.
>
> My file generates 3 reports, each on a separate worksheet. The data is 68
> columns wide & 8000 rows deep (and growing). I inserted a couple of columns
> to run off the user menu. In other words. the user selects the criteria
> that s/he
> wants to see, and these columns look at the codes in the data and return info
> that determines whether that row of data is something that the report should
> include. Then I sumif based on those columns.
>
> So far, we've decided to make the calculations a little more linear.
> Instead of
> 3 sheets calculating off the data sheet, I now have one sheet calculating
> off the data sheet and the other two sheets calculating off the first sheet.
>
> Does this help? Does it even make sense?
>
> Many thanks for your previous suggestions.
>
> Mickey
>
> "Dave Peterson" wrote:
>
> > Nope.
> >
> > But pivottables aren't made to, either. If your data that creates the
> > pivottable is quite large, then refreshing the pivottable could be slow, too.
> >
> > So you'd be just exchanging one slow tool for a different slow tool.
> >
> > Some options...
> > Teach the users to click anywhere on the pivottable and hit the refresh icon (!)
> > on that pivottable toolbar.
> >
> > If you want to refresh the pivottables on a given sheet (dedicated to the
> > pivottable), you could add some code to the worksheet_activate event to update
> > just those pivottables.
> >
> > Option Explicit
> > Private Sub Worksheet_Activate()
> > Dim myPT As PivotTable
> > Application.EnableEvents = False
> > For Each myPT In Me.PivotTables
> > myPT.RefreshTable
> > Next myPT
> > Application.EnableEvents = True
> > End Sub
> >
> > There are other things you could do, too. You may want to post a little more
> > info to make any response relevant.
> >
> > mikxtr wrote:
> > >
> > > Maybe. But I need the report dynamic enough to generate different subtotals
> > > based on input from the user. Can a pivot table recalculate itself on the
> > > fly (e.g.
> > > without someone specifically recalculating the table)? If so, how?
> > >
> > > Thanks,
> > > Mickey
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Maybe using a pivottable will be quicker.
> > > >
> > > > mikxtr wrote:
> > > > >
> > > > > I have a file that is relying upon "sumif" statements to sum a plethora of
> > > > > data. Ok, it may not have too many rows, but rows X columns = slow file. At
> > > > > least, that's what I'm thinking. Does anyone have any suggestions on how to
> > > > > make excel more computationally efficient?
> > > > >
> > > > > Many thanks,
> > > > > Mickey
> > > > >
> > > > > P.S. I'd move it to Access, but then the powers-that-be will never read the
> > > > > report.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks, Dave. I would talk to my boss, but I understand his concern:
Files with macros sometimes get opened by others with macros disabled,
thus causing issues if your file is macro dependent. I would argue him on
this point (like I do daily), but with Excel 2002, I have opened files many
times without enabling macros simply because Excel did not prompt me.
(And yes, I know about the security level, etc, but it still chooses not to
work ... randomly.)

So this time, I smile, nod and make do. Thanks again for all of your
suggestions.
Now off to reading about calculations ....

Mickey



"Dave Peterson" wrote:

> You may want to visit Charles Williams' web site:
> http://www.DecisionModels.com/calcsecrets.htm
>
> He has a few tips that may help.
>
> But you may want to talk to the boss once more. If you can make your program
> work via code and Access, then you might be reducing the number of user errors
> (although the number of developer errors could go up <vbg>).
>
> But if your code can eliminate much of the grunt work, I would think that the
> final results would be more, er, trustworthy.
>
> Remind him or her that a computer is supposed to make those mundane tasks that
> can be mechanized much more consistent (and probably lots faster!).
>
>
>
> mikxtr wrote:
> >
> > Well, here's the catch: no macros. What I'd prefer to do is store the data
> > in an
> > access table and pull only the necessary data through macros. But I've been
> > restricted by my boss from macros (we're trying to minimize user-error),
> > so there it is.
> >
> > My file generates 3 reports, each on a separate worksheet. The data is 68
> > columns wide & 8000 rows deep (and growing). I inserted a couple of columns
> > to run off the user menu. In other words. the user selects the criteria
> > that s/he
> > wants to see, and these columns look at the codes in the data and return info
> > that determines whether that row of data is something that the report should
> > include. Then I sumif based on those columns.
> >
> > So far, we've decided to make the calculations a little more linear.
> > Instead of
> > 3 sheets calculating off the data sheet, I now have one sheet calculating
> > off the data sheet and the other two sheets calculating off the first sheet.
> >
> > Does this help? Does it even make sense?
> >
> > Many thanks for your previous suggestions.
> >
> > Mickey
> >
> > "Dave Peterson" wrote:
> >
> > > Nope.
> > >
> > > But pivottables aren't made to, either. If your data that creates the
> > > pivottable is quite large, then refreshing the pivottable could be slow, too.
> > >
> > > So you'd be just exchanging one slow tool for a different slow tool.
> > >
> > > Some options...
> > > Teach the users to click anywhere on the pivottable and hit the refresh icon (!)
> > > on that pivottable toolbar.
> > >
> > > If you want to refresh the pivottables on a given sheet (dedicated to the
> > > pivottable), you could add some code to the worksheet_activate event to update
> > > just those pivottables.
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Activate()
> > > Dim myPT As PivotTable
> > > Application.EnableEvents = False
> > > For Each myPT In Me.PivotTables
> > > myPT.RefreshTable
> > > Next myPT
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > There are other things you could do, too. You may want to post a little more
> > > info to make any response relevant.
> > >
> > > mikxtr wrote:
> > > >
> > > > Maybe. But I need the report dynamic enough to generate different subtotals
> > > > based on input from the user. Can a pivot table recalculate itself on the
> > > > fly (e.g.
> > > > without someone specifically recalculating the table)? If so, how?
> > > >
> > > > Thanks,
> > > > Mickey
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Maybe using a pivottable will be quicker.
> > > > >
> > > > > mikxtr wrote:
> > > > > >
> > > > > > I have a file that is relying upon "sumif" statements to sum a plethora of
> > > > > > data. Ok, it may not have too many rows, but rows X columns = slow file. At
> > > > > > least, that's what I'm thinking. Does anyone have any suggestions on how to
> > > > > > make excel more computationally efficient?
> > > > > >
> > > > > > Many thanks,
> > > > > > Mickey
> > > > > >
> > > > > > P.S. I'd move it to Access, but then the powers-that-be will never read the
> > > > > > report.
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

I've never seen excel do that (open workbooks with macros disabled if I choose
to enable them)...

But I get your drift with the PHB!



mikxtr wrote:
>
> Thanks, Dave. I would talk to my boss, but I understand his concern:
> Files with macros sometimes get opened by others with macros disabled,
> thus causing issues if your file is macro dependent. I would argue him on
> this point (like I do daily), but with Excel 2002, I have opened files many
> times without enabling macros simply because Excel did not prompt me.
> (And yes, I know about the security level, etc, but it still chooses not to
> work ... randomly.)
>
> So this time, I smile, nod and make do. Thanks again for all of your
> suggestions.
> Now off to reading about calculations ....
>
> Mickey
>
> "Dave Peterson" wrote:
>
> > You may want to visit Charles Williams' web site:
> > http://www.DecisionModels.com/calcsecrets.htm
> >
> > He has a few tips that may help.
> >
> > But you may want to talk to the boss once more. If you can make your program
> > work via code and Access, then you might be reducing the number of user errors
> > (although the number of developer errors could go up <vbg>).
> >
> > But if your code can eliminate much of the grunt work, I would think that the
> > final results would be more, er, trustworthy.
> >
> > Remind him or her that a computer is supposed to make those mundane tasks that
> > can be mechanized much more consistent (and probably lots faster!).
> >
> >
> >
> > mikxtr wrote:
> > >
> > > Well, here's the catch: no macros. What I'd prefer to do is store the data
> > > in an
> > > access table and pull only the necessary data through macros. But I've been
> > > restricted by my boss from macros (we're trying to minimize user-error),
> > > so there it is.
> > >
> > > My file generates 3 reports, each on a separate worksheet. The data is 68
> > > columns wide & 8000 rows deep (and growing). I inserted a couple of columns
> > > to run off the user menu. In other words. the user selects the criteria
> > > that s/he
> > > wants to see, and these columns look at the codes in the data and return info
> > > that determines whether that row of data is something that the report should
> > > include. Then I sumif based on those columns.
> > >
> > > So far, we've decided to make the calculations a little more linear.
> > > Instead of
> > > 3 sheets calculating off the data sheet, I now have one sheet calculating
> > > off the data sheet and the other two sheets calculating off the first sheet.
> > >
> > > Does this help? Does it even make sense?
> > >
> > > Many thanks for your previous suggestions.
> > >
> > > Mickey
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Nope.
> > > >
> > > > But pivottables aren't made to, either. If your data that creates the
> > > > pivottable is quite large, then refreshing the pivottable could be slow, too.
> > > >
> > > > So you'd be just exchanging one slow tool for a different slow tool.
> > > >
> > > > Some options...
> > > > Teach the users to click anywhere on the pivottable and hit the refresh icon (!)
> > > > on that pivottable toolbar.
> > > >
> > > > If you want to refresh the pivottables on a given sheet (dedicated to the
> > > > pivottable), you could add some code to the worksheet_activate event to update
> > > > just those pivottables.
> > > >
> > > > Option Explicit
> > > > Private Sub Worksheet_Activate()
> > > > Dim myPT As PivotTable
> > > > Application.EnableEvents = False
> > > > For Each myPT In Me.PivotTables
> > > > myPT.RefreshTable
> > > > Next myPT
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > There are other things you could do, too. You may want to post a little more
> > > > info to make any response relevant.
> > > >
> > > > mikxtr wrote:
> > > > >
> > > > > Maybe. But I need the report dynamic enough to generate different subtotals
> > > > > based on input from the user. Can a pivot table recalculate itself on the
> > > > > fly (e.g.
> > > > > without someone specifically recalculating the table)? If so, how?
> > > > >
> > > > > Thanks,
> > > > > Mickey
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Maybe using a pivottable will be quicker.
> > > > > >
> > > > > > mikxtr wrote:
> > > > > > >
> > > > > > > I have a file that is relying upon "sumif" statements to sum a plethora of
> > > > > > > data. Ok, it may not have too many rows, but rows X columns = slow file. At
> > > > > > > least, that's what I'm thinking. Does anyone have any suggestions on how to
> > > > > > > make excel more computationally efficient?
> > > > > > >
> > > > > > > Many thanks,
> > > > > > > Mickey
> > > > > > >
> > > > > > > P.S. I'd move it to Access, but then the powers-that-be will never read the
> > > > > > > report.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson