How to base a pivot table on another pivot table?

  • Thread starter Thread starter Seamus O'Connell
  • Start date Start date
S

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.
 
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.
 
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.
 
Hi Tim

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

Regards
Roger Govier

Tim said:
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
Click to expand...

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

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top