Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotals (Excel 2000)

    I have a spreadsheet with subtotals. Is there a formula/macro that can automatically insert a row above and below a subtotal. The placement of the subtotal line changes each month. A search on the subject did not find a match. Thank you.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    Cindy, are multiple subtotals stored on one sheet in separate columns on different rows, or are they all on the same row, just different columns, or are they on different sheets? (gosh I hope you understand that...example below)

    One Sheet, different columns, different rows:
    <table border=1><td>SUBTOTAL 1</td><td> </td><td> </td><td>SUBTOTAL 2</td><td> </td><td> </td><td>SUBTOTAL 3</td></table>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    One Sheet, different columns, different rows.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    In military terms: Roger. Could you show a simple example? Does your sheet actually say the word Subtotal? If so, I'm assuming it would look like this:


    <table border=1><td><center>A</center></td><td><center>B</center></td><td><center>C</center></td><td><center>D</center></td><td><center>E</center></td><td><center>F</center></td></tr><td>SUBTOTAL 1:</td><td>4500</td><td></td><td></td><td>SUBTOTAL 2:</td><td>5000</td><td></td><td></td><td></td><td></td><td>SUBTOTAL 3:</td><td>5500</td></table>

    In which case the macro will have to select not only the SUBTOTAL cell, but also the one next to it... <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    Your sample is exactly correct. It is a typical data subtotal. On the subtotal row, the name of the field is used and then the word 'Total'. I had wanted to insert a blank row above and below each subtotal so it would stand out. Almost like a conditional format, but for data subtotals.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    I understand...I'm just playing around with keeping the Find from looping back to the beginning (sorta like a circular reference) and pushing your subtotals to the bottom of the worksheet <img src=/S/woops.gif border=0 alt=woops width=58 height=36>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    Cindy,
    Is there a set number of occurances of "Subtotals", or does it vary?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (Excel 2000)

    It varies every time the data subtotals is run.

Posting Permissions

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