Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Pivot Table Retains Old Source Data in Addition to New Source Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pivot Table Retains Old Source Data in Addition to New Source Data

    Hi All,

    Not sure if anyone has encountered this before. I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a "pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

    Does anyone know how to purge the old data from the internal Pivot Table memory?

    I hope this is enough information....let me know if you need more.

    Thanks in advance for any help,

    Jon

  • #2
    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

    To manually clear the old items from the list,first drag the pivot field out of the pivot table. on the Pivot toolbar click the Refresh button, finaally drag the pivot field back to the pivot table
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Pivot Table Retains Old Source Data in Addition to New Source Data

      Hi,

      Try,

      Sub DeleteMissingItems2002All()
      'prevents unused items in non-OLAP PivotTables
      'http://www.contextures.com/xlPivot04.html
      'in Excel 2002 and later versions
      'If unused items already exist,
      'run this macro then refresh the table
      Dim pt As PivotTable
      Dim ws As Worksheet

      For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
      pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
      pt.PivotCache.Refresh
      Next pt
      Next ws

      End Sub
      Kris

      ExcelFox

      Comment


      • #4
        Re: Pivot Table Retains Old Source Data in Addition to New Source Data

        Hi Again,

        Thank you for your help. Both methods appear to get the old items off of the list. Unfortunately, my file size is not coming down (the 2005 file is just over half the size of the updated 2006 file). I guess its just an internal issue with Excel and whatever memory/database it creates for the pivot table. I wonder if there is a way to bring this up to Microsoft...have either of you every done anything like that?

        Thanks again,

        Jon

        Comment


        • #5
          Re: Pivot Table Retains Old Source Data in Addition to New Source Data

          Under the Pivot Tables Options there are choices under Data Options.
          Last edited by Dave Hawley; September 8th, 2006, 12:26.

          Comment


          • #6
            Re: Pivot Table Retains Old Source Data in Addition to New Source Data

            Hi Dave,

            Thanks for your reply. I've got the "Save Data with Table Layout" option unchecked on both the old 2005 report and the updated 2006 report. For some reason the 2006 report is just much larger when I save it (even having cleared out the old 2005 items). My worry is that I'll either have to recreate the report each year (its huge to begin with) or that it will just keep growing and growing each year.

            Thanks,

            Jon

            Comment


            • #7
              Re: Pivot Table Retains Old Source Data in Addition to New Source Data

              This often happens if you include mnay extra rows in your PT Source Data range. The best way is to use a Dynamic Named Range and base your PT off that, e.g =MyRange

              Also, see this page Unusual Excel File Size Increase

              Comment


              • #8
                Re: Pivot Table Retains Old Source Data in Addition to New Source Data

                Hi Dave,

                I can't seem to get my pivot table to work with the dynamic range. The data for the pivot table is stored in an external workboook (report in one workbook and data-source in a second workbook shared by multiple reports)...could this be causing the problem?

                Also, a side question: Is there a defined Microsoft track for becoming something like a "Certified" Excel or Office developer? I have a CompSci background and started out after school working in a Financial Environment -- my position didn't involve any development work so I found myself using Excel VBA as an outlet for my skills. I'm now working as a business/revenue analyst in a company's Sales department. I've gotten pretty good at Excel and VBA but have had no formal training. I like Excel and figure that since this is the path that I am headed down I figure I should formalize my training to carve out a specific career path for myself (I've been out of school for about 3.5 years now and I'm not headed in any specific direction...I feel that I would be more successful if I had a longer-range career path laid out).

                Thanks,

                Jon

                Comment


                • #9
                  Re: Pivot Table Retains Old Source Data in Addition to New Source Data

                  Hi John

                  Yes, that would be the problem. Dynamic ranges wont work when reference from another Workbook. However, go here Excel Hacks Workbook Download Examples and donwload Chapter 4 and it shows how you can easily pull in the needed data using Workbook Open Event

                  Comment


                  • #10
                    Re: Pivot Table Retains Old Source Data in Addition to New Source Data

                    Oops, forgot. Please start a New Thread for your question regarding certification etc so the Thread Title matches.

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X