Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    pivot tables (XP)

    I've been asked to create a rather complex pivot table. I know the theory behind it, so no problems there.

    What I do want to happen is that the pivot table goes across the columns with the results rather than in rows.

    So for instance - Results and then 2000 - 2001 - 2002 - 2003 - 2004

    I have attached a simplified version of the spreadsheet. The pivot table collects the data from all sorts of tables and this way I can check directly whether all the information is correct, as the information is in the same format.

    Its kinda hard to explain, so I hope you understand

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pivot tables (XP)

    I don't think you want a pivot table here, but an outline:
    - Select B10:M10, and click the AutoSum button.
    - Click in any cell in the table, then select Data | Group and Outline | Auto Outline
    - You can now hide/show the detail rows by clicking the outline buttons on the left hand side.
    See the attached version.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: pivot tables (XP)

    Outline is this instance isn't the best option. Some sheets have about 1400 rows with information. The spreadsheets are send to various departments where managers fill out their budgets. They shouldn't alter the sub-totals (protected but still).

    I have to do about 150 workbooks and all have different amount of rows and totals.

    So by creating a pivot table I can check by using conditional formatting if the numbers are correct and a lot faster.

    Thanks anyway for your help

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pivot tables (XP)

    The data aren't laid out correctly for what you want. They should look like this:

    <table border=1><td>RCL</td><td>Period</td><td>Amount</td><td>a101</td><td align=right>04/05 </td><td align=right>10</td><td>a101</td><td align=right>05/06</td><td align=right>20</td><td>a101</td><td align=right>06/07</td><td align=right>30</td><td>a101</td><td align=right>07/08</td><td align=right>40</td><td>...</td><td align=right>...</td><td align=right>...</td></table>
    You can then easily create a pivot table the way you want. See attached. It also includes the crude macro I used to transform the data layout.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: pivot tables (XP)

    Thanks for that, will show the results to my manager and hopefully this is what he wants
    Thanks again

Posting Permissions

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