Display Pivot Table Based on Defined Name List

[Luke9311]

Hello - I have a worksheet where I have a drop down list and 6 names to pick from (we'll name this worksheet "Selection"). Additionally, I have 6 other worksheets, each containing one pivot table that belongs to one of the 6 names on my drop down list.

I would like to pick any of the 6 names from my drop down list, and have their associated pivot table show on the "Selection" worksheet.

Is there any way to make this happen? I thought it would be a fairly simple matter of defining each pivot table as a named range, merging a bunch of cells on the "Selection" worksheet to be the same size as the pivot table, and then doing an INDIRECT() formula to pull the data over, but I've had no luck.

Running:
Excel 2010
Windows 7 (64 bit OS)

Thanks in advance

 

[pbornemeier]

I cannot figure a way to do it with formulas.
A VBA solution:
Assuming blank rows on Selection for rows 10 and down
On a change in the Drop down list, clear used range on and below row 10. Copy the associated pivot table to cell A10 of the Selection worksheet.

 

[Fazza]

If the pivot tables are based on similarly structured data, what about making a pivot table from all data and having a page field to filter for the sub-set you want to see?

A little more complicated would be to make the pivot table from the cell with the drop down as well as the other data, then upon changing the cell with the dropdown just refresh the pivot table.

I think other than those ways you'd need VBA.

 

[Luke9311]

Thank you both for your ideas. For this example I have decided to change course a little and use the slicers with a pivot table containing all my data right on the main worksheet.