Creating Pivot Tables from Multiple Worksheets

[khw312]

Hi,

I was hoping to create a pivot table from multiple worksheets that consist of multiple tabs. I am finding that I have too many elements within my data source to do this. Is there a solution other than making the world's largest master worksheet? Please advise.

My elements are:

• Year
  • 2012 and 2013
• Week
  • All 52 weeks within the year
• Channel
  • 4 Channels
• Category
  • ​There are a lot of these.
• Subcategory 1 - Don't need this for pivot
• Subcategory 2 - Don't need this for pivot
• Vendor
• Sales ($)
• Units
• Margin %

I highlighted the key elements for the pivot table. I'd like to be able to create three (3) different pivot table views. Please advise.

Thanks,
Kim

 

[Jerry Sullivan]

Hi Kim and Welcome to the Board,

Using an SQL query is a good way to consolidate data from multiple worksheets as a PivotTable source.

This thread provides some instructions on how to do this for xl2007 and later
http://www.mrexcel.com/forum/excel-...table-multiple-consolidation.html#post3412102

If your using an earlier version of Excel, MVP Fazza has many threads on this forum that provide instructions for those versions.