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