+ Reply to Thread
Results 1 to 4 of 4

Dealing with a large table and multiple calculations

  1. #1
    scrabtree23
    Guest

    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.

  2. #2
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    if you can code the calculations into vba you could then simply write the results in the appropriate locations as values from a macro- sounds like in your case it would be more efficient to add the vba code than to store all the formulas in the spreadsheet.
    not a professional, just trying to assist.....

  3. #3
    JulieD
    Guest

    Re: Dealing with a large table and multiple calculations

    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.




  4. #4
    scrabtree23
    Guest

    Re: Dealing with a large table and multiple calculations

    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.

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1