Force pivot tables to use same source data
 

I have 5 pivot tables in a workbook. They all used the same source data (ie,
$a$1:$x$10000), but I decided I wanted a 6th which required a new column in the
data table.

If I create a new pivot table with the same data range as a previous one, Excel
asks me if I want to use the same data range. How do I do the reverse? How do I
update old pivot tables to use the more current range (ie, $a$1:$y$10000)?

--
Regards,
Fred

Force pivot tables to use same source data
 

I don't think you can force it.

But you could select each pivottable, then open the wizard and go back to where
you specified the range and change it there.

But you may want to consider using a dynamic range that can grow and contract
based on how many rows (and columns) you're using.

Debra Dalgleish shares this:
http://contextures.com/xlNames01.html#Dynamic

If you want the columns to resize, too, you could modify her formula from this:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
to
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

This kind of dynamic range trusts that you don't have any empty cells in column
A or row 1 (except at the bottom and far right of the table)--and nothing under
the table or to the right, too.


Fred Smith wrote:

I have 5 pivot tables in a workbook. They all used the same source data (ie,
$a$1:$x$10000), but I decided I wanted a 6th which required a new column in the
data table.

If I create a new pivot table with the same data range as a previous one, Excel
asks me if I want to use the same data range. How do I do the reverse? How do I
update old pivot tables to use the more current range (ie, $a$1:$y$10000)?

--
Regards,
Fred

--

Dave Peterson

Force pivot tables to use same source data
 

Thanks, Dave.

I did use the wizard to go back and change the source range, but it didn't look
to me like it saved any space (although I don't know how to tell for sure).

I'll either live with it, or recreate the pivot tables. I like Deb's dynamic
range idea.

--
Regards,
Fred


"Dave Peterson" wrote in message
...
I don't think you can force it.

But you could select each pivottable, then open the wizard and go back to
where
you specified the range and change it there.

But you may want to consider using a dynamic range that can grow and contract
based on how many rows (and columns) you're using.

Debra Dalgleish shares this:
http://contextures.com/xlNames01.html#Dynamic

If you want the columns to resize, too, you could modify her formula from
this:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
to
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

This kind of dynamic range trusts that you don't have any empty cells in
column
A or row 1 (except at the bottom and far right of the table)--and nothing
under
the table or to the right, too.


Fred Smith wrote:

I have 5 pivot tables in a workbook. They all used the same source data (ie,
$a$1:$x$10000), but I decided I wanted a 6th which required a new column in
the
data table.

If I create a new pivot table with the same data range as a previous one,
Excel
asks me if I want to use the same data range. How do I do the reverse? How do
I
update old pivot tables to use the more current range (ie, $a$1:$y$10000)?

--
Regards,
Fred

--

Dave Peterson

Force pivot tables to use same source data
 

And after you set up one pivot table to use a dynamic range as its
source, there's code here that will set all the pivot tables to use the
same cache:

http://www.contextures.com/xlPivot11.html

Fred Smith wrote:
Thanks, Dave.

I did use the wizard to go back and change the source range, but it didn't look
to me like it saved any space (although I don't know how to tell for sure).

I'll either live with it, or recreate the pivot tables. I like Deb's dynamic
range idea.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Force pivot tables to use same source data
 

Thanks Debra. ChangePivotCache was just what I wanted, once I figured out I had
to change Sheets("Pivot") to one of my sheets with a pivot table in it.

It changed my 90Mb file to 30Mb.

--
Regards,
Fred


"Debra Dalgleish" wrote in message
...
And after you set up one pivot table to use a dynamic range as its source,
there's code here that will set all the pivot tables to use the same cache:

http://www.contextures.com/xlPivot11.html

Fred Smith wrote:
Thanks, Dave.

I did use the wizard to go back and change the source range, but it didn't
look to me like it saved any space (although I don't know how to tell for
sure).

I'll either live with it, or recreate the pivot tables. I like Deb's dynamic
range idea.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Force pivot tables to use same source data
 

You're welcome. Thanks for letting me know that it worked, and that's
quite a reduction in file size!

Fred Smith wrote:
Thanks Debra. ChangePivotCache was just what I wanted, once I figured out I had
to change Sheets("Pivot") to one of my sheets with a pivot table in it.

It changed my 90Mb file to 30Mb.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html