Excel Line Chart - 12 month chart moving with dates

[rockycj]

Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting. The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.

Attached is an example of a line chart that I started. Do I need to do a macro or VBA coding for this?

CC

[rylo]

Hi

Can you amend your example to show the source data on another sheet in the same workbook. Make sure the data is not sensitive / confidential.


rylo

[rockycj]

Attached is the data in the worksheet, it's only Test data. The users only want to see the past 12 months of graphing, depending on what month end it is. For example if it's Jan 2008 month end, the users will be entering the data in the source document worksheet in Feb 2008 until the 5th business day. Then they want the chart for Feb 2007 - Jan 2008. If it was Feb 2008 month end they would want the chart for Mar 2007 - Feb 2008.

They also want the option of going back to view previous months graphs, so say they wanted to go back to Jan 2005. Then they would want Jan 2005 - the current month-end charting.

Could you help out with how Excel knows what the current month-end it is and how to tell it to go back 12 months? Also how to put an option in for selecting previous months earlier than the current 12 months?
Sorry I'm a beginner at programming in Excel.

CC

[rylo]

CC

Have a look at the attached file.

There is a dropdown in Graph!A30. If you pick the user defined option, you need to pick a date from B31. If you pick current, it will default to the rolling 12 months.

There are a heap of defined names on Data that control it all.

HTH

rylo

[rockycj]

This works great! I tried to duplicate what you did but unsuccessfully. Can you inform me of your steps or send me a good website for reference? Sorry I am brand new to this and am spinning my wheels.

Thank you!

CC

[rylo]

Hi

On sheet data, there are a series of defined names.

Have a look at the formulas in each of those names. Look at them in order of DateRange, rolling, undefined, dataa and datess.

If you pull the formulas in those names apart, you should get some idea on what each one is doing.

Then go to sheet Graph.

In A30 and B31 there is some data validation. Select the cell, go data, validation and have a look at the way there were constructed.

Finally, look at the graph series 1 ranges. They are defined names. So when the data in the defined name changes, the graph will update.

If you need explanation of the formulas, come back with specific questions.


rylo

[rockycj]

Can you explain what the Rolling define does for the Count(Data!$5:$5)-11?
I understand that it represents the columns and its referencing the Data worksheet but I don't understand what $5:$5 means then -11 ( I think that means go back 11 months?


CC

[rylo]

Hi

Count(Data!$5:$5) counts the number of numbers in row 5. So as you add new actuals, it will increase.

As this part of an offset function, it will determine the last filled column. By subtracting 11, you come back to the first month of the rolling 12 months.

The next part of the command will count 12 columns in the range.

You can take the formula out of the name definition, and put it into the spreadsheet. Then evaluate each part of it and you can see what it is doing.

rylo

[rockycj]

The request has changed on the graphing, now they only want the past 13 months of the current month for graphing. So if they were working on Jan 2008, the graph would be Jan 2007 - Jan 2008. There will be no selection of months.

Anyway, I attempted this with the Controllable Waste % and it's not working. Can you take a look and let me know what is wrong? I'm wondering if my columns for the data and/or values in not correct.

Thank you for all your help!

CC

[rockycj]

The only thing that is wrong now is when I add data to the next month, the data moves as it's suppose to. But the Month name does not move with it.

Can you take a look at this and ignore the previous email?
THANK YOU!

CC

[rockycj]

Rylo,
I just added the "datess" defined name and now the dates are also rolling as data is added.

Thank you so much for all your help. If I got any other questions, I'll post it.

CC

[rylo]

CC

Glad you managed to get it going.


rylo

[Flyddo]

Hi Rylo

Please can you make all the graphs rolling for 12 months? Please?

[rylo]

Flyddo

Please read the forum rules. Rule 2 states

2). Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.

Remove your question from this thread, and start a new post.

You can refer to, and reference this post in your new post.

rylo

[meekness37]

I'm trying to do essentially the same thing but my data goes vertically instead of horizontally. Also, I have more than one string of data and it's organized in a table. What do I change in the "rolling" range to accommodate this?

This is what I tried, but I know I've got something backward.
=OFFSET(Table2[[#Headers],[Month]],COUNT(Table2[Incidents]),0)-11,12,1)