Pivot Refrest with New Data?

R

roadkill

I have a file, including a pivot table, that I would like to use as a
"template" without having to regenerate the pivot table each time. In other
words, I would like to add to the data or replace the data and then just do
the equivalent of a refresh on the pivot table. Is this possible? From what
I've seen, the pivot table is locked into a certain range so if new data
doesn't fit the old range, portions of the data can be ignored. Any thoughts?

Thank you,
Will
 
O

Oliver J. Teapot

It's perfectly and wonderfully possible.
It's the magic side of the PT.

But you need the PT in one file and the data in another.
Use the External Data option when you start the Pivot Table Wizard.

And you have to be able, as far as I know, to be able to referece the folder
that contains the file as Network Drive.
Even if it is on your own PC! I think it's known as a "UNC" type reference.
Say you have a shared folder that contains the data on a server, you have to
set up a "Network Drive" from Windows explorer to that shared folder and
give it Drive Letter such as "Z:" or "K:" or whatever.
So for your PC the shared folder known as "OurData" on "BigCompanyServer"
can become "Z:"
Then you tell the Pivot table you want to use the data on Z:.

If the external data is on your own PC, same principle applies.
Connect network drive \\MyPCName\MySharedDataFolder


Then when you save your PT, choose "save as" and choose XLT for a template
and then !!! Excel will realise that you are using External Data and will
ask you whether tou want to save the template Empty or not. Empty means that
the External data will be re-read i.e *** autoupdated ***the next time
someone opens the Template.

oliver
 
D

Dave F

The pivot table toolbar has a refresh button on it--it looks like an
exclamation point. Just make sure that the data you add to your pivot table
range is contiguous with the initial range.

Example: if your initial range ends at row 50, enter new data in row 51, not
row 52.

Dave
 
R

Ron Coderre

If
the pivot table and the source data are in the same workbook
and
the pivot table does not include new source data rows when they are added

Then
you might consider basing your pivot table on a Dynamic Named Range.

A Dynamic Named Range automatically expands and contracts based on the
number of records in the data....No VBA programming necessary.

For instructions, see Debra Dalgleish's website:
http://www.contextures.com/xlPivot01.html#Dynamic


Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
R

roadkill

Thanks, Ron. The named range works great.
Will

PS
As for the earlier suggestion about just needing for the data to be
contiguous - as mentioned in my initial email, I tried that and only the data
in the original range is refreshed. Nothing outside that range is included -
contiguous or not.
 
Top