Excel 2007 : Pivot table from multiple sheets or other suggestion to collect data!!!!

Hi, can any one help me again!!!

I have a huge service spread sheet to gather data from, and i only know pivot tables to some degree.

basically each product has a pass and fail and total for each week and month on seperate tabs. I have done a lookup table to give me year,month and week fields but,the pivot tables i have done, and the way ive done the dates keeps all the info along the same line.

So under pivot data tab is all the data collected and at BD-BI is all the relivant data added up,with the calculations i need to report on.

next i thought i'd pivot that data so i can produce some charts for year,week and result.(chart located BK60)

on the face of it it looks ok,but if i pick week 1 2012,it picks week 1 2011+2013 too. I dont want that as i may want to compare week 1 2011 to 2012 at some stage.

1) how can i arrange the pivot to do what i want, or if not

2) is there a better way of getting the info out of the seperate sheets to produce pivots ,or

3) can the forum members suggest through experience a different approach to getting data with some noobie guides to getting the results, maybe by using this attached excel as an example for me to learn !! as there are so many functions to do different things i dont know about.

I need to be able to get results for each product for each week and month( total pass, fail and pareto the fails for each product)

i look forward to any help or suggestions......

Ever Get an answer? I too want to mine multiple tabs with one pivot table and this thread was a possible solution, but it doesn't look like it eas ever answered?

If you have since found a solution, would you be willing to share?

Thanks,
Grasstoe