Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mimic Pivot Table (2000/XP)

    I have a spreadsheet that is rather large (33 MB) and contains a lot of data (7000 Rows x 47 Columns).

    I use pivot tables to calculate and summarize the data.

    It seems the pivot tables are part of what is making the spreadsheet so big (the data itself is just under 3 MB). I need the data the pivot tables refer to so that others can view the "proof" behind the numbers.

    What I'd like to do is to have only the values the pivot tables generate, and use a hyperlink or some sort of automation to bring up the value details. (Double-clicking on a pivot table value opens the details of the selected value in a new worksheet).

    Any suggestions?

    Thanks in advance!

  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: Mimic Pivot Table (2000/XP)

    <P ID="edit" class=small>(Edited by sdckapr on 03-Mar-04 13:27. added PS)</P>The pivot table setup might be able to be optimized in terms of speed and/or memory. Check out some of the options in this microsort article

    Steve
    In addition you might check the CacheIndex properties of each of your pivot tables. When you create the pivot table it asks if you want to save memory by basing the pivot table on an existing one. If you choose no, it keeps another copy of the datacache. If you base them all on separate ones it will eat up the memory. You can change the ChacehIndex of all to the same one (CacheIndex = 1) and your memory use for the pivot tables will be reduced.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Pivot Table (2000/XP)

    I have set all the pivot tables to the same data set, but it is still quite large (20MB).

    Just the data itself in a spreadsheet would make it 7MB.

    The pivot tables are used to calculate, then the different summaries are tallied on another sheet. After calculation, the pivot tables are not used except to drill down the data sets. If I can use one of the summary fields as a hyperlink and mimic the drill down, I can get rid of the pivot tables and associated calculations.

  4. #4
    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: Mimic Pivot Table (2000/XP)

    If you are NOT saving the data with the Table layout and all the caches are the same, I know if know other ways to reduce. Do your pivot tables have calcs? this could be contributing to the size also.

    I would guess that formulas might take up even more space than the pivot tables. Most formulas that would mimic it are array formulas and they can make the worksheet calc very slow and use a lot of resources.

    You could mimic the results with a macro to read the data, summarize it and manipulate it and then spit out the results. This would require you to do all the coding that is "built into" the pivots: finding unique items, formatting etc.

    I don't understand what you are describing here:
    <hr>If I can use one of the summary fields as a hyperlink and mimic the drill down, I can get rid of the pivot tables and associated calculations. <hr>
    so I can not help with that. Could you explain what you are trying to accomplish?

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mimic Pivot Table (2000/XP)

    <hr>You could mimic the results with a macro to read the data, summarize it and manipulate it and then spit out the results. This would require you to do all the coding that is "built into" the pivots: finding unique items, formatting etc.<hr>
    Or alternatively record and write macros that create the entire PT, then copy, paste special values and format the range of the PT and delete the PT's.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    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: Mimic Pivot Table (2000/XP)

    An excellent approach and should be simpler coding than just dooing it all yourself.

    Steve

Posting Permissions

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