• Excel 2000 Pivot Table Appends

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Excel 2000 Pivot Table Appends

    • This topic has 8 replies, 4 voices, and was last updated 24 years ago.
    Author
    Topic
    WSBillSutterfield
    AskWoody Lounger
    March 12, 2001 at 2:33 pm #353821

    In Excel 2000 I have a Pivot Table with source data that is updated weekly. I would like to have the new data in the source list updated in my current Pivot Table. How can I do this?

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSAndrew Cronnolly
      AskWoody Lounger
      March 12, 2001 at 3:11 pm #518456

      My best approach to this is to name your data source range as Database. When it is updated ensure that the new Data is included in that range.

      If your pivot table is pointed at Database for the data source, all you need do then is select Refresh Data on the Pivot Table toolbar (it’s the exclamation symbol, ! ).

      The table should update with the new data.

      Reply | Quote
      • WSBillSutterfield
        AskWoody Lounger
        March 12, 2001 at 7:37 pm #518486

        This is GREAT! Thanks.

        Reply | Quote
        • WSepic60sman
          AskWoody Lounger
          March 16, 2001 at 11:24 am #519167

          I creat a named range that expands and contracts to contain additional or less rows of data. To do this enter the following into the refers to box at the bottom of the ISERT,NAME,DEFINE Dialog Box:
          =offset($A$1,rowsdown,columnsRight,counta($D:$D)-1,width)
          where:
          rowsdown and columnsRight are used to locate the upper left corner of the data table (substitute numbers for these) and similarly, “width” specifies the number of columns in your data table. Last, counta($D:$D)-1 counts the number of rows in the data table (assuming that column D is one of the columns and that you have one row used for a header.
          Example: Data begins in cell A1 and has 10 columns:
          =offset($A$1,0,0,counta($D:$D)-1,10).
          Last, you enter the name you gave this range in as the Pivot Table range and you are set.
          Stephen

          Reply | Quote
          • WSBillSutterfield
            AskWoody Lounger
            March 17, 2001 at 7:03 pm #519300

            Although I have used offset to create dynamic graphs I had not thought of using it for the pivot table. This works exactly like I needed. THANK YOU!

            Reply | Quote
          • WSMichael Evans
            AskWoody Lounger
            March 18, 2001 at 9:11 pm #519337

            I can see a potential problem here as you are looking at a column in the body of your data, which might contain blank cells. CountA counts only the data containing cells, so your defined range might be some rows short of the whole database. (PT’s still work OK if there are some blank cells in the data).

            The following, very similar, solution was given by DanielD Maramba in the old Lounge:

            Datarange = Offset($A$1,0,0,CountA(Offset($A$1,0,0,9999,1)),5).

            If you have more than 9999 rows your need to increase this figure.

            The safest way to find the last row in a sheet is to go to the end row (65536 in XL97, 2000?) and then come up to the last used row above this, cannot see any reason why this could not be incorporated in the datarange definition.

            Reply | Quote
            • WSepic60sman
              AskWoody Lounger
              March 18, 2001 at 9:55 pm #519341

              You are certainly correct about the COUNTA function failing to count blank cells in the data. Maybe, I should have explained this. I aviod the problem by always using a field that never has blanks or which always has the same number of blanks like the column with my row names. Once you nail this, you just have to adjust the constant you subtract at the end of the offset function.
              But your (or Maramba’s) alternative may be better. However, I don’t quite understand why CountA(Offset($A$1,0,0,9999,1)) wouldn’t work the same way as counta($A$1:A9999), in which case there would be no advantage to using the “OFFSET” function. When I am in doubt as to whether or not the Range covers all my data, I simply call up INSERT, NAME, DEFINE and click on the equation. This immediately outlines the data with dashes. Then I simply Scroll down to the bottom of the table. Also, if in dou is always easy to check on the existance of blank cells in the column one chooses.

              Reply | Quote
            • WSMichael Evans
              AskWoody Lounger
              March 26, 2001 at 4:11 am #520229

              Agreed Stephen, I had never used the formula so I had not looked at it critically.

              Since XL spits the dummy if you try to make a PT with a column with no heading, counting the number of non-blank cells in Row 1 will always be a valid way of determining the number of columns. The easiest way of doing this is COUNTA($1:$1). So if you make sure there are no blanks in Column A the following should define the datarange alright:

              Datarange = OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)

              Defining data ranges if there may be blank rows present is a bit of a problem, the following solution is not terribly elegant but I have not been able to make it fail yet.

              Start your data range in cell B1. In cell A1 put the following formula:

              =IF(COUNTA(B1:IV1)>0,ROW(),0)

              fill down to the end of Column A. Every row with an entry now has its Row number in Column A. All other rows have 0 in Column A. Now define the datarange:

              =OFFSET(Sheet1!$B$1,0,0,(MAX(Sheet1!$A:$A)),COUNTA(Sheet1!$B$1:$IV$1))

              Of course if you have other data on your sheet, in rows not used in your data range, you will have trouble. This includes your PT, so make sure you do not put it on the same sheet your data is on!

              Reply | Quote
    • WSMichael Evans
      AskWoody Lounger
      March 12, 2001 at 3:11 pm #518457

      This is based on XL97 as I don’t have XL2000.

      If your updated data are in the same range as your old data just click the Refresh button.
      If they are in a different range, call up the PT Wizard, go back to the data source page, enter the new range, and click the finish button.
      If you are just adding more data to the existing data you can automatically add this to your PT using the offset function in the data source page.

      Reply | Quote
    Viewing 1 reply thread
    Reply To: Excel 2000 Pivot Table Appends

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.