Display Pivot Table Based on Defined Name List

Luke9311

New Member
Joined
Dec 23, 2013
Messages
5
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Dec 23, 2013
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,675
Messages
5,524,220
Members
409,565
Latest member
Suilenroc

This Week's Hot Topics

Top