How can I create a monthly chart so I can compare each month's performance?

[USAMax]

I have data going back to January 2012 that currently stretches through the end of 2013, I will be collecting more data.

I want to see if there is a trend per month. Are some months doing better than other months?

If I use a PivotTable it says January and January 2, this is the same for each month. Originally I had the first day of each month but that gave me one very long graph of 24 months and that is not what I want. I only want a graph of 12 months and each of the ranges on the left column are as follows.

0 to 1,000
1,000 to 5,000
5,000 to 10,000
10,000 to 20,000
20,000 to 30,000
30,000 to 40,000
40,000 to 50,000
50,000 to 60,000
60,000 to 70,000
70,000 to 80,000
80,000 to 90,000
90,000 to 100,000
110,000 to 120,000
120,000 to 130,000
130,000 to 140,000
140,000 to 150,000
150,000 to 160,000
160,000 to 170,000
170,000 to 180,000
180,000 to 190,000
190,000 to 200,000
200,000 to 250,000
250,000 to 300,000
300,000 to 350,000
350,000 to 400,000
450,000 to 500,000
500,000 to 1,000,000

In my fantasy world I would like it to show 0-1000 for 2012 and 2013, January - December. Then I hit a down arrow and it would show 1000 to 5000. I don't think this is possible so I may have to have a lot of charts.

I have been programing for about 20 years but I have NO experience with graphs. In this case I want a basic line graph. I am a complete novice at graphs as you might guess but I can follow instructions.

 

[C Moore]

Axis can't be linked directly to a cell. You would have to do data validation for your dropdown list, and use a worksheet change event to update your axis. The code below should do the axis change, you'll have to setup the named ranges that are used.

Sub Scale_axis()
Dim cht As ChartObject
For Each cht In ActiveSheet.ChartObjects
    cht.Chart.Axes(xlValue).MaximumScale = Range("Chart_Max_Count").Value
    cht.Chart.Axes(xlValue).MinimumScale = Range("Chart_Min_Count").Value
Next cht
End Sub

 

[USAMax]

Thank you so much Mr. Moore, I cannot wait to try that. Unfortunately I cannot create the graph and that is the first hurdle I need to cross.
Row 1 has 12 columns merged with the value 2012 and another 12 columns merged with the value 2013.
Cell B2 has January through M2 with December and N2 starts with January again.

The problem is that I want a line for year 2012 on B3 to M3 for, "0 to 1,000" and another line for year 2013 on N3 to Y3.
Instead, I get one line that represents from January 2012 to December 2013.

 

[C Moore]

So you just want 12 months across the bottom so your lines would overlap, a different one for each year?

 

[USAMax]

Yes that is correct.

 

[USAMax]

Good morning Mr Moore.

Can you tell me if I need to restructure my data with a different line for each year? That would be confusing as the first two lines would say 2012 0-1000 and 2013 0-1000.
I am looking forward to trying your automation but restructuring the data will be a pain as I want to add another year.

 

[C Moore]

I would use the name manager. Create a new name, say yr_2012, refers to =offset($b$2,0,0,1,12). Insert a line chart, add series =Sheet3!yr_2012. yr_2013 ould be =offset($b$2,0,0,12,12). axis along the bottom should be 1-12, add a legend and name the series