How to base a pivot table on another pivot table?

[Seamus O'Connell]

In Excel 2007 I can't work out how to create a pivot table based on another
pivot table.

I have got a workbook created in Excel 2003 which has:
- a first worksheet containing a large table of raw data
- a second worksheet containing a minimal pivot table called Base_table
which refers to the data in the first worksheet
- further pivot tables in the second worksheet and in further worksheets
which are based on Base_table

This structure is intended to minimise the space used by the pivot tables
and to enable a change to the data to be propagated through all the pivot
tables when Base_table is refreshed.

I have saved the original Excel 2003 workbook as an xlsx file in Excel 2007.

In Excel 2007 refreshing Base_table does appear to refresh the pivot tables
based on it.

But in Excel 2007 when I create a new pivot table in the workbook I can't
see any way to base it on Base_table. In Excel 2003 I could select Base_table
from a list of the pre-existing pivot tables in the workbook. In Excel 2007
all I can apparently do is go to the worksheet containing Base_table and
select its cells, which I have not tried as it seems unlikely that that is
how we are supposed to do it.

 

[Debra Dalgleish]

If you base subsequent pivot tables on the same source data as the
Base_table, they'll automatically share the same pivot cache. That's the
same effect as the Excel 2003 option to base a pivot table on another
pivot table.
Or, you can type Alt+D, then type P, to open the old PivotTable Wizard.

 

[Tim]

I have a similar issue. I would like to use the filters set in PTable 1 to
also filter the results in PTable 2. In Excel 2003 (if I recall correclty),
when you used the same pivot table cache for both tables, the filters would
be applied to both.

Is there a way to do this - apply the filters of one pivot table to another
- without using VB code? Or is code required? Are there after-market add-ins
that do this?

Thanks.

 

[Roger Govier]

Hi Tim

Debra has a downloadable example file which shows how to do this
http://www.contextures.com/PivotMultiPagesChangeAll.zip

Regards
Roger Govier

I have a similar issue. I would like to use the filters set in PTable 1 to
also filter the results in PTable 2. In Excel 2003 (if I recall
correclty),
when you used the same pivot table cache for both tables, the filters
would
be applied to both.

Is there a way to do this - apply the filters of one pivot table to
another
- without using VB code? Or is code required? Are there after-market
add-ins
that do this?

Thanks.





__________ Information from ESET Smart Security, version of virus
signature database 4856 (20100210) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4856 (20100210) __________

The message was checked by ESET Smart Security.

http://www.eset.com