Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping and Subtotals

    I developed a forecasting table that runs the width of the spreadsheet (to IV in XL 97), with row AD1 to IV1 being workdays and the cells below having projected minimum production required by project (about 75 rows). Projects need to be grouped for subtotals by about 5 to 10 types and, unless I can Group across the date-columns first, I am going to have to check off every day in the miniscule "Add Subtotal To" window within the small SUBTOTAL Dialog Box. But, I don't seem to be able to Group the columns into month-columns. I put in headers under a top row with mm/yy and Grouped by columns. The Line and + sign appeared but when I clicked it all the data disappeared. If it would help, I could attach a file.
    Any help will be appreciated.
    <img src=/S/threadhead.gif border=0 alt=threadhead width=28 height=31>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Grouping and Subtotals

    Have you tried formatting the date column as mmm then sort. When sorting, and the dates are not all the same year, click options, and set the first key sort order to Jan, Feb, Mar,.. etc. You should then get a subtotal on based on the date(month) field.
    If I misunderstood, please post a sample of the wb.

    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Subtotals

    Andrew:
    I sent a long reply intending to attach the file but the attachment could not getthroough our fireall. So, I deleted it. Eventually, I was able to solve my subtotals problem without using the subtotal functionality. I did this by building in Vlookups with Off set function that defined the lookup array. I never gave it more than 20% shot to work but it worked beutifully (made the file a bit large but it zips right through the calculations). I do still want to be able to do the grouping.
    Re ypur suggestion: the months are the headers for the columns, so I can not sort them. I tried inverting (rotating) everything and it got real messy. I tried using yyyy-mm format (rather use the yr first in case I want to sort the results later) but no help with the Group function. I have used Group before (maybe it was with a PT) and did not have this problem. I have tried simple examples and can not get it to work. Even if I could put all the data in a PT in order to use the Group Functionality, I still would have to enter all those dates in as pivot table fields.
    Thanks again for your kind assistance
    Stephen


    [img]/w3timages/icons/spook.gif[/img]

Posting Permissions

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