Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Venice, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summarizing Data From Several Sheets (2000)

    Hi,
    Here goes.
    I have 28 Sheets in an accounting workbook. Sheet 1 is a summary of Information (months, net income, disbursements and balances). Sheet two is a detailed view. Sheets 3 through 15 are Jan through Dec (Each with 7 Columns: Date, Check#, Description, Deposit, Disbursement, Balance and Account #). Sheets 16 Through 28 are a breakdown of each of 13 account numbers. I am trying to filter the data from sheets 3 through 15, into separate Account # sheets, 16 through 28.

    Problem #1
    Using Advanced Filter, I can only select one WS at a time.

    Problem #2
    When using a Pivot Table across multiple ranges, I can't seem to choose the columns I want for Records. ie. I would like Date, Check#, Description, Deposit & Disbursement to be in the row area, as this data should not be processed in any way. I would like Balance to disappear. I would like the table to sort by Account #. And finally, an additional field to subtract disbursement and add deposit in their respective record, with the result of the same field from the previous record.

    Problem #3
    Clipit has flipped me off numerous times, and refuses to show himself. Rocky only comes out of the house for a bathroom break on my screen. And Genius is bald, curled up in the fetal position above my start button muttering incoherently. They are all pretty tired of me. hehe.


    I have about 200 lines of code that basically do what I need, in a very cumbersome, lengthy manner. I used Advanced Filter and paste, Looping each month sheet 13 times to send the data to the acct sheet. There has to be a better way. Any help would be appreciated.

    TIA
    Todd

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summarizing Data From Several Sheets (2000)

    I have answered similar queries of this nature with the opinion that the workbook setup should be modified.

    I found the best way to work with something like this, would be to COMBINE Sheets 3-15 into 1 sheet with the 7 columns and keep the data in a "table" type of format.

    Having in 1 table allows the use of filtering, (auto and advanced) d-functions, array functions, pivot tables, etc, sub totals: all sorts of things. You could even add columns for other items to help filter (month, year, task)

    If you need a "printout" in a certain form, I would just create with formulas (or a macro) to extract the data for that form. You could select by month or something else

    Advantages, less worksheets, form can be updated easier (only 1 form) and calculations are much easier to work with. You'd be able to do all the things with more-"typical" excel features without having to pull your hair relying on a jury-rigged solution.

    A little bit of time in redesign will save you lots of coding and headache in the future.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Venice, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing Data From Several Sheets (2000)

    I Have thought about that, and considered the possibility. Problem is these sheets have about 200 Records (Rows) each, and most of the time the data is viewed electronically. The spreadsheet needs to be user friendly for people viewing it electronically as well as those that input the data. One sheet would make filtering the data very easy, but input and viewing more difficult. No headers for January, February... at the top of each month would be possible, as these would break the table, and render the filtering impossible. I appreciate the input however.

    Cheers,
    Todd

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Summarizing Data From Several Sheets (2000)

    Todd,

    not that I have any answers but

    >Sheets 3 through 15 are Jan through Dec

    so what is the 13th month of the year? Just curious. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Fred

    PS In general I'd agree with Steve.

Posting Permissions

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