Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotal (Excel 2003)

    A user that just got upgraded to Excel 2003 is having trouble using Data | Subtotals.
    The data is first sorted by Account and Sub-Class.
    Then they request subtotals of dollars after each change in Account and then another subtotal is run for every change in Sub-Class.
    Please look at the attached Cut-Down workbook. She is having trouble getting the Subtotal for Account to be after the last Subtotal for Sub-Class within that account. I do not do much with Subtotals and am not able to help her. She said it worked the way she wanted when she did it in Excel 97. She also indicated it works as she expected it would on someone elses PC that is also running Excel 2003. Is there a switch somewhere that may be set different on her PC?

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    Is this more what you expected?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    Legare,

    Thanks for the fast reply!
    Yes that is what she described to me as what she used to get.
    How did you accomplish it? The workbook sample I sent is much smaller than the original, so I hope it is not a lot of custom work.....
    (Is is done the same in Excel 97 and Excel 2003?)

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    I simply removed all of the original sub-totals in the worksheet. Then I put dashes in the empty columns in row 1 so that Excel could properly figure out where the data is. Then I put subtotals back for the Acct column, then for the sub class column. Then I deleted the dashes I added to row 1.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    Legare,

    Thanks. I made a dummy sheet just to see how it worked. I left blank columns between the data columns. It worked just as I would expect.
    I am not sure why the workbook subtotaled properly on one PC and not on another, but I will pass your info on to the person.
    Thanks for taking the time to figure it out and for explaining how you did it.
    Have a great day.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    I use XL2K, so the problem with blank columns may have been fixed in 2003.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    Legare,

    I do not think the blank columns were the issue.
    After getting yours steps, I tried them on the original full size workbook. It failed (as described in earlier post).

    Then I saved the sheet as html and reopened it, saving it as an excel workbook and did the following steps.
    I tried the subtotals again -failed.
    I deleted the blank columns.
    I tried the subtotals again -failed.
    I converted entries that looked like numbers (acct and Sub-Class) to full text (with ' in front of each entry).
    I tried the subtotals again -failed.
    I may try a few other things as I think of them, but I have told the user that if your first suggestions did not work -they are out of luck.
    This is indeed a strange one.

    I do want to thank you for your efforts.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    New Lounger
    Join Date
    Feb 2006
    Location
    Sheffield, Yorkshire, United Kingdom
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    The problem is that Excel doesn't know what range you want to subtotal, because of the gaps between the columns.
    Remove all the Subtotals, then add a "space bar" entry in cells B1, D1, F1 etc (i.e. make sure that the heading row has something in every column) then Excel finds it much easier to determine the range to subtotal.
    Then select DataSubtotals and it works perfectly... well it did on mine anyway! (I'm running Excel 2003)
    Hope this helps

    Stuart

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal (Excel 2003)

    Stuart,

    That made sense when Legare suggested it too. But, I went so far as to delete the blank columns (after putting headings in did not work).
    It still sorts strange. Yet when I created a new set of data, hand entered, even with blank rows - it worked fine. That was one of the reasons I thought maybe it was corrupt. So I took steps to get around that (saved as html, etc). Still -no joy. I think she should trade PCs with the person that owns the one that does it OK. <g>

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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