Thread: comlpicated charting

Ok fellas, this is a very complicated one, so I'm searching for a VBA champion.
I need to make this graph using. All the data to be used is in the sheet 'DataforGrid' under the 2nd Table. Ineed the graph to look like 'Chart B' which I have pasted from another source under the coloured Heatmap. The way it should work is that there will be 2 scroll bars to select 2 dates. Then based on those 2 dates - two lines are graphed one for each date. you can ignore the bar charts on 'chart B'....the 2 lines will have 8 data points (as there are 8 Terms for each product on the heatmap grid, and 8 products for each Term)
So what data will these 2 lines cover?

Look at the heatmap - you see Product and Term. Outside the coloured boxes you can see the categories of product and term. Row 4 has product, and Column B has Term.
What I want to do is be able to click on say cell B7 (which is 1m), when I do this i want to graph the values of (1m in 1y, 1m in 2y, 1m in 5y, 1m in 7y and so on till 1m in 30y)----lets call it horizontal charting. That is 8 values/points on the line, im not charting these entire series but rather, just for the 2 dates that were selected in the scroll bar...this is to show the difference between the values of the term/product on those dates.
now the same thing needs to be done vertically so if i select a Product 30y....it will chart --1m in 30y, 3m in 30y, 6m in 30y all teh way to 10y in 30, thats 8 points.
So in total there are 16 different cells that I should be able to click, 8 product, 8 term.


If we are charting a 'Term' then x-axis will have the 8 products and the chart label will be the 'Term' category-eg. 5Y, Y axis will have the values on that day(ignore the 2nd y-axis on Chart B which has some weird figures


(****In sheet ' DataforGrid' you will see categories like 3m in 7y, etc the first item is always term, the 2nd is product
so remember its 'Term in Product')

I hope I wasnt confusing, if so let me know and I will try to explain better

any help would be much appreciated, I understand this can be quite difficult so my expectations are not that high

thanks for reading, and good luck (i need more than just luck here)