Pivot table refresh

[tmarsh7407]

I have a table of data from Access that I paste into a sheet titled "Data" in
Excel as a data source, field names and all. The pivot table is in another
sheet of the same workbook. I've checked to be sure the data source is the
tab I am using (Data!A$:AC$). Even though I paste into the same place and
have much different data now the same percents that I had before still show
up when I run my calculated amounts. All of the headings are the same as
before. I even tried a "Refresh data" but it made my calculated fields
return zeros. Any ideas what went wrong? Is a "Refresh" from the data in
another tab of the same workbook necessary? How can I be sure it is using
the new data and not remembering the old data somehow?

 

[Jim Thomlinson]

There is no need to paste the data from Access into XL. You can hook your
pivot table directly to the Access Database (generally a better way to go as
now you are not tied to 65,536 records). In the second step of the process to
create the pivot table you are asked to select a data source. Instead of
selecting the default which is a range of cell, select External Data. Now
select MS Access and then browse to the database table you want to use.

It is important to note that Pivot tables create their own memory space
seperate from the source data (known as the pivot cache). This memory
contains the pre-aggregated amounts. Any time your source data changes you
need to refresh the pivot table to update the cache. You can specify that the
pivot table updates on oper or at a set timer interval.