Formula to consolidate different pivots table into one

Let’s consider below scenario:


I have 5 different Pivot tables (different source per each pivot); each pivot table may not have all the product names or may not have all the weeks (either nothing sold in that particular week or a particular product didn't sell in a particular week)

So in order to summarize the different pivot tables into one I created another table which includes a list of all products in a column and the week numbers per month in a row (I’ll have to update the Week numbers every month); the issue here I am not successful in consolidating all different pivot table into one table as some may have all products over the 4 weeks of the month and some may not. Please check attached file as an example and please help me out adjusting my formula:

Current formula used:


=IF(ISERROR(IF(B$54=B$11,VLOOKUP($A55,$A$11:$G$19, 2,FALSE),0))=TRUE,0,IF(B$54=B$11,VLOOKUP($A55,$A$1 1:$G$19,2,FALSE),0))+IF(ISERROR(IF(B$54=B$25,VLOOK UP($A55,$A$25:$G$37,2,FALSE),0))=TRUE,0,IF(B$54=B$ 25,VLOOKUP($A55,$A$25:$G$37,2,FALSE),0))+IF(ISERRO R(IF(B$54=B$43,VLOOKUP($A55,$A$43:$G$51,2,FALSE),0 ))=TRUE,0,(IF(B$54=B$43,VLOOKUP($A55,$A$43:$G$51,2 ,FALSE),0)))

Thanks in advance for your help,
Tarek

Thanks for your feedback, I gave the (PT0007 - Multiple Consolidation Ranges) in http://www.contextures.com/excelfiles.html#Pivot a shot by coping my set of data on top of the one in file: PivotMultConsolProv.xls then saved and closed it then i opened PivotMultConsolPivot.xls and updated the pivot table and found the following:

my headings didn't appear in the pivot table; however the new data appeared under the old headings!. Also, there is some sort of limitation built in the MS Query as it only returned a partial coverage of my data and the entire list.

Thanks again for you support on this matter.

Tarek