Dealing with a large table and multiple calculations

I have a very large table on a sheet named "Data". One column has date, one
has provider, one has type, one has county (in other words, multiple sort
criteria), along with about a hundred columns with provider data.

My staff wanted a customised report that would, by question, sumarize last
years data vs. this year's data, vs. this months data. They also wanted this
by county and by provider and by type. (In other words, a report that looked
at the Data sheet and made many, many, many caculations).

I created a canned report sheet, that ties to a worksheet sheet. This
worksheet has about 30 rows by 100 columns of array formulas which make all
the necessary calculations. While this ultimately does what I want, it is
very slow while calculating. Does anyone have a general suggestion to point
me in a different direction. I have tried using DataSum, but it hasn't
proved effective.

Hi

have you investigated pivot tables?

check out www.contextures.com/tiptech.html

for ideas on what can be done with these.

Cheers
JulieD

"scrabtree23" <scrabtree23@discussions.microsoft.com> wrote in message
news:5880E6ED-EC68-41D2-965B-13168228DBD6@microsoft.com...
>I have a very large table on a sheet named "Data". One column has date,
>one
> has provider, one has type, one has county (in other words, multiple sort
> criteria), along with about a hundred columns with provider data.
>
> My staff wanted a customised report that would, by question, sumarize last
> years data vs. this year's data, vs. this months data. They also wanted
> this
> by county and by provider and by type. (In other words, a report that
> looked
> at the Data sheet and made many, many, many caculations).
>
> I created a canned report sheet, that ties to a worksheet sheet. This
> worksheet has about 30 rows by 100 columns of array formulas which make
> all
> the necessary calculations. While this ultimately does what I want, it is
> very slow while calculating. Does anyone have a general suggestion to
> point
> me in a different direction. I have tried using DataSum, but it hasn't
> proved effective.

I'll see what I can do with a pivot table. Thanks.

"JulieD" wrote:

> Hi
>
> have you investigated pivot tables?
>
> check out www.contextures.com/tiptech.html
>
> for ideas on what can be done with these.
>
> Cheers
> JulieD
>
> "scrabtree23" <scrabtree23@discussions.microsoft.com> wrote in message
> news:5880E6ED-EC68-41D2-965B-13168228DBD6@microsoft.com...
> >I have a very large table on a sheet named "Data". One column has date,
> >one
> > has provider, one has type, one has county (in other words, multiple sort
> > criteria), along with about a hundred columns with provider data.
> >
> > My staff wanted a customised report that would, by question, sumarize last
> > years data vs. this year's data, vs. this months data. They also wanted
> > this
> > by county and by provider and by type. (In other words, a report that
> > looked
> > at the Data sheet and made many, many, many caculations).
> >
> > I created a canned report sheet, that ties to a worksheet sheet. This
> > worksheet has about 30 rows by 100 columns of array formulas which make
> > all
> > the necessary calculations. While this ultimately does what I want, it is
> > very slow while calculating. Does anyone have a general suggestion to
> > point
> > me in a different direction. I have tried using DataSum, but it hasn't
> > proved effective.
>
>
>