Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 Pivot Table Appends

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    This is GREAT! Thanks.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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!

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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.

  9. #9
    DaveHawley
    Guest

    Re: Excel 2000 Pivot Table Appends

    In case you are interested you can use some simple VBA code to create your named range for you:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("Data").UsedRange.Name = "MyRange"
    End Sub


    This needs to be placed in the Workbook module. To access this, right click on the sheet picture (top left next to file) and select "View Code". Then simply paste in the above code. You will of course need to change the sheet name to suit.

    The code will automatically re-define the range "MyRange" each time you save. "MyRange" will incorporate ALL data on the sheet down to the last row and across to the last Column. If this is not suitable, let me know and it can be modified to suit!

    Dynamic named ranges are great and I have some more examples of them on my web site. The only draw back with them is a Pivot Table has problems with them if the range is not within the same Workbook.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Pivot Table Appends

    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!

Posting Permissions

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