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

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






"roadkill" a écrit dans le message de
news: ...
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

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
--
Brevity is the soul of wit.


"roadkill" wrote:

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

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


"roadkill" wrote:

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

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.


"Ron Coderre" wrote:

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


"roadkill" wrote:

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