updating a pivot table with current year's data

[Takeadoe]

Folks,

I've got a Master Pivot table with the following dimensions:

Rows=County (n=88)
Column=Year (8 now, 1 to be added each year)
Data=number of entries (10,817 now, more to be added each year)


This Pivot table covers years 1997-2005. Each year I would like to add
the current year's data, in this case 2006. In a recent post, I
described the current year's data as follows:

It comes to me as a list of county names. This year, that list
contained 618 entries (counties). I created a Pivot table to summarize
by county. Now I'm at a loss as to how to get the 2006 data added to
the Master Pivot Table. Things that I've explored/considered:

1) paste the data from 2006 into the source range for the Master Pivot
table and then simply redefine the data range for the Master Pivot
Table to include the 2006 data (not sure how to redefine the range
though!).

2) possibly use VLOOKUP using county - not sure how this would work
exactly???

Would someone be willing to help guide me here?

I would really appreciate it.

Mike

 

[Roger Govier]

Hi

Assuming your data has a row format which contains County as one of its
columns and year as one of its columns, then just append the new years
data to the existing data table.

Create a dynamic named range called Data.
Insert> Name > Define> Data
Refers to =OFFSET($A$1,0,0,COUNTA($A:$A), x)
where x refers to the total column width of your data table.
The width will remain fixed, at whatever value you substitute for x in
the above formula, but the depth of the table will grow according to the
value returned by COUNTA for the number of rows with data in column A.

On the PT, use the dropdown on the PT toolbar, to select Wizard. Click
Back, and where the data range is currently defined, replace it with
=Data.
Click Finish.

The PT will now calculate using the expanded set of data, and, if there
are more year values in the column containing Year in the source table,
the column entries in the PT will automatically expand to create a
column for the new year.

 

[Takeadoe]

Roger,

Thank you very much for taking time to help. I stumbled onto the
OFFSET function and man, that is the answer! Once again, thanks for
the help!

Mike