Combining 2 pivot tables into 1 chart

Delete duplicate post.

If you don't mind doing a bit of a two-step, you can make this happen. I reproduced the pivot tables on Sheet5 and reformatted them. I used helper columns in Columns F and M to make "composite keys" of the Month and Year.

I highlighted the range J6:M50 and gave it a static name: Sales_Data.

In Column G, I look up the sales data: =INDEX(Sales_Data,MATCH(F6,(Sales_Data M:M),0),3) ... The (Sales_Data M:M) gets the intersection of the two ranges: Sales_Data and Column M:M. In other words, Column M:M from rows 6 to 50.

All helper columns are extended down to row 50. This should keep you going for a couple of years.

Then I made a series of named dynamic ranges.
Plot_Basic =OFFSET(Sheet5!$C$6,0,0,COUNTA(Sheet5!$C:$C)-2,1)
Plot_Battery =OFFSET(Plot_Basic,0,1)
Plot_Date =OFFSET(Sheet5!$A$6,0,0,COUNTA(Sheet5!$A:$A)-2,2)
Plot_KGO =OFFSET(Plot_Basic,0,2)
Plot_Sales =OFFSET(Plot_Basic,0,4)
Sales_Data =Sheet5!$J$6:$M$50

See this article to see how to make named dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

I then assigned these ranges to a chart. See this article on dynamic charting: http://www.utteraccess.com/wiki/inde...namic_Charting.

As long as the complete set of data doesn't extend beyond row 50, you should be good. When you refresh the pivot tables on Sheet7 these pivot tables should also refresh since they are built from the same source data. The dynamic ranges will stay up with the plotting.