Trailing twelve weeks

I am trying to graph the last 12 weeks of data on a line chart for several locations and am currently going in and manually changing the source data on a weekly basis for 8 graphs with 12 locations per graph. It is pain staking and I feel there should be an easier way to do this but am not sure what it would be. So I am wondering is there a way to set up a graph to pull the last 12 data points on a row?

Suppose the X and Y data are in A2:B34 (headers at top). Then create two named ranges:

Sheet1!SeriesX Refers to: =OFFSET(Sheet1!$A$1, MATCH(9E+307, OFFSET(Sheet1!$A$1, 1, 0, 65536 - ROW(Sheet1!C3), 1)) -11, 0, 12, 1)

Sheet1!SeriesY Refers to: =OFFSET(Sheet1!SeriesX, 0, 1)

Then change the series on the chart to

=SERIES(,Sheet1!SeriesX,Sheet1!SeriesY,1)

Now if you add data to the bottom, it will always plot the last 12 values.

Can you dummy up some representative data and post a workbook?

I am about to head home from work but will try to this evening.

Ok attached is the file. Any help would be appreciated

Is this even possible with the way the table is set up? If not could you suggest a better way to set the table up. In the actual workbook each location has its own tab and that is where this data is pulled from.

I think you should rearrange the data in a database (tabular) format. So you would have these columns (at a quick glance):

Region (a through aq)
Date of Launch
Data Type (A/R, Total Orders, Order Entry Errors, Invoice Prep Errors)
Weeks 1 through 40

No blank rows.

Then you can use a pivot table and all sorts of other tools to analyze and plot data.

It can be done -- not trivially, but with some effort. Putting the data all together as a single table would be a good start.

Then you can make a second table (on another sheet) where you select the region and type of data, and it computes the range of cells to plot (the last 12 weeks of data) for each series.

Doing it manually is error-prone, as I expect you've found.

yes very much so!! It looks like I have a good weekend project. Thank you for the Ideas. I am sure I will run into more questions on this but for now I will try rearranging it like you have suggested and see how that goes. I want to get this as automated as possible so I can prevent mistakes and save time.

BTW, the reason for having Total Orders as a data type, is that typically in a database you shouldn't have derived data (percent of stuff), just the raw data.

Can you please elaberate on the database (tabular) format. I started setting it up with the columns like you suggested but my question is if the weeks still go across the top are you not still stuck with the same problem. Then when I try to run a pivot table on it every week is set as a field. Is there another way to run the pivot table.

What I have tried doing is setting up another table with an offset function to pull the last 12 weeks. This seems like it will work but why did I need to set it up any different, I could have done the same thing with the table I had. I feel like I am missing something here.

I have uploaded the new file and included the 12 week tab that I have been working on setting up with the offsets. Can someone let me know if there is a better way to do this before I put too much time into it.

Did you ever figure out a resolution for this problem? By looking at your data, I think you can make it work. I am willing to help any way I can.

I think you want to use Define Names to set up ranges for each location (BPT & COR) and information type (A/R).