Excel 2000 Pivot Table Appends

Topic

New Reply

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

Replies

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

This is GREAT! Thanks.

Reply | Quote

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

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

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

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

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

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