Rolling 12 month graph

**Harvey132** · 07-06-2015, 11:22 AM

Hi All!!

I need some assistance regarding charts! So similarly to everything else on Excel I am fairly useless when it comes to charts! I have been asked to create a chart which shows the most recent 12 months of data.. The raw data shows data from January 2014 until current date. This data gets added to on a monthly basis so this month Jun-15 will be added, next Jul-15 and so on.

Taking the last 12 months as an example, that will show from May-14 to May-15 on the graph and we will manually update at the end of this month to show from Jun-14 to Jun-15.

Is it possible to either make the graph automatic so when new data is entered it chucks forward a month or perhaps write a macro that moves the graph to select the most recent 12 months of data?

Any assistance would be very much appreciated.

Many thanks
Harvey

**mikeTRON** · 07-06-2015, 11:38 AM

Hey Harvey,

So I would recommend breaking this project of yours into bitesized pieces for you.

It sounds like you want to accomplish the following:
1) determine how to automatically determine the most recent month from a dataset that gets updated
2) determine how to then summarize the data for that month and the 11 preceding months
3) setup a graph to display that dynamically changing data

If you do it in phases I think you will find it is much easier.

Can you post a sample/desensitized dataset? I can probably help you whip something up to get you going in the right direction.

**Harvey132** · 07-06-2015, 11:40 AM

Hi Mike,

Thank you for getting back to me so quickly! Okay so this is helpful!

So I need assistance on those steps, can you help me out at all?

**mikeTRON** · 07-06-2015, 11:41 AM

Hey Harvey,

Sure, can you post a sample desensitized dataset?

**Harvey132** · 07-06-2015, 11:42 AM

Unfortunately the data being used is confidential, is there any other way around it?

**mikeTRON** · 07-06-2015, 12:08 PM

Yeah, create a SAMPLE dataset. Otherwise I am just talking hypothetical solutions and rely on you to know how to adapt them to the problem you have.

So take the layout of the data, remove or replace all sensitive information with nonsense names or data. Leave the dates and numbers in the data. You can even randomize the numbers with just nonsense. The data doesn't matter at all, the layout DOES. The idea is so YOU can have a solution created for your exact problem, if that makes any sense.

I can use words all day and I can tell you what I would do rather simply, BUT it will be much easier for you if I just give you a model that is already complete so you can play with it and see what I did.

**ChemistB** · 07-06-2015, 12:08 PM

We don't need to see the data, just the way it's set up. all the data can be fictional/random. (go advanced>Manage attachments)

It sounds like you want to learn about dynamic named ranges (ranges that change as you add or subtract data). You can then use these Names in your series. See attached simple example