Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Pivot with two total columns? (2K-SR1)

    I am trying to design a pivot table with dates as row headings, customer number as column headings, to analyze sales variances. I'm getting a 'Grand Total' row and column, but I also want to have a cumulative-to-date grand total as well. (See attached) The number of customers to be included in this table is going to continue to grow to over 100, but not all at one time, so I will need to have the columns generated as part of the pivot table. Any ideas or suggestions?

  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: Pivot with two total columns? (2K-SR1)

    I think you will need a macro.

    The macro only needs to run the code to create the pivot table, then it just has to add the values down the column just past the pivot table.

    If you record the steps with the macro recorder, it should get you almost 90% of the way there.
    If the pivot is on a separate sheet, you add the code to the sheetactivate event and so whenever the sheet with the pivot is activated, the pivot will be updated and the column generated.

    If you need further assistance, please post back, and I can try to offer some details.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot with two total columns? (2K-SR1)

    I tried to use a macro to create the pivot table but couldn't quite get it the way I wanted, but it did give me time to re-think my strategy. Instead of using the pivot table to calculate the totals, I calculated the totals and had them included in the data pulled into the pivot table:

    <table border=1><td>Date</td><td>Customer</td><td>Variance</td><td>1/1/05</td><td>Cust1</td><td>1.00</td><td>1/1/05</td><td>Cust2</td><td>2.00</td><td>1/1/05</td><td>Cust3</td><td>3.00</td><td>1/1/05</td><td>Week Total</td><td>6.00</td><td>1/1/05</td><td>YTD Total</td><td>6.00</td><td>1/8/05</td><td>Cust1</td><td>4.00</td><td>1/8/05</td><td>Cust2</td><td>5.00</td><td>1/8/05</td><td>Cust3</td><td>6.00</td><td>1/8/05</td><td>Week Total</td><td>15.00</td><td>1/8/05</td><td>YTD Total</td><td>21.00</td></table>
    The detail data was pulled onto one worksheet. On a second worksheet the 'Week Total' was calculated using a SumIf formula, while the 'YTD Total' is a simple C2+B3 formula. A macro copies all the data (pasted as values) onto the sheet that feeds the pivot table. When the pivot table refreshes it treats the two Total values as if they were normal 'Customer' values, resulting in a table looking like:

    <table border=1><td>Date</td><td>Cust1</td><td>Cust2</td><td>Cust3</td><td>Week Total</td><td>YTD Total</td><td>1/1/05</td><td>1.00</td><td>2.00</td><td>3.00</td><td>6.00</td><td>6.00</td><td>1/8/05</td><td>4.00</td><td>5.00</td><td>6.00</td><td>15.00</td><td>21.00</td></table>
    I'm thinking that this method could make it easy to build a pivot table with multiple total columns.

Posting Permissions

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