Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have a workbook that has one worksheet per month with the daily amounts in each column and the category for each amount on the rows. the objective is to have the category and daily amounts for each month place on one worksheet with the 365 days for the year going accross the worksheet.

    Another way would be to have each day and category for each amount be one record and then summarize on a pivot table to drag the dates across.

    I am attaching a spread sheet on this issue. Any thoughts on the best way of combining the worksheets or making them records to be reported in a pivot table.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I tried summarizing by date for one of the months using the SUMPRODUCT and CONDITIONAL SUMMING (SUMIF) but it reports back to the summary page either a #Value or #N/A. Any ideas??

  3. #3
    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
    Create a pivot table with multiple consolidation ranges, then select each of the ranges on each of the sheets.

    [Note:You will run into a problem with pre-XL2007 workbooks as you are limited to 256 columns, not enough for a whole year....]

    Steve

    PS see attached...

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    What are multiple consolidation ranges in this case?

  5. #5
    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
    [quote name='MNN' post='797534' date='12-Oct-2009 12:36']What are multiple consolidation ranges in this case?[/quote]

    Each of the individual worksheet ranges ...

    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
  •