Dynamic Charting with Multiple Charts

You may have to make 18 names! That is if they are 9, totally independent data sources. You'd need a name for the date and another for the metric being tracked. Of course, I am assuming the worst case.

First a couple of wikis that may help:
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

And if you want some help with this particular set of charts, it would help a lot to attach a sample file.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

This looks very do-able. I will probably get to it tomorrow.

One side question: how does the data get entered? Is it manual or copy/paste?

Here it is. I am using the techniques mentioned in the links in my previous post.

I converted the information on the Daily Charts sheet to an Excel Table. Excel Tables automatically copy down formulas and they know how big they are and they are addressable by column title name. When you copy/paste data to the end of the table, the table expands to include the data so formulas, pivot tables and charts built off the table still reference the correct amount of rows.

The offset calculation sheet is used to calculate some of the key numbers needed to make the named ranges.

Cell B1 looks at the date and gets the maximum date: =MAX(Table_Data[Date])

Cell B2 finds the row, relative to the table, where this date is found: =MATCH(B1,Table_Data[Date],0)

Cell B3 backs off 29 days (you want 30 and we already include the last day): =B2-29

Next I define a range for the date to plot. Plot_Date =OFFSET(Table_Data[[#Headers],[Date]],'Offset Calculations'!$B$3,0,30,1)

The Table_Data[[#Headers],[Date]] happened when I selected that cell as the start point. Normally tables are helpful in formulas. In this case, it actually makes things less clear. This reference is Cell B5 on the Daily Charts sheet.

So, the formula could just as well been written =OFFSET('Daily Charts'!$B$5,'Offset Calculations'!$B$3,0,30,1)

The basic offset formula has 5 arguments: start point, rows to go down, columns to go right, rows to return and columns to return. So
We start in Cell B5,
Go down the number of rows calculated bu cell B3 on the Offset Calculations Sheet,
Go right zero columns,
and return a range 30 rows deep and one column wide

With the current data this is the range B35:B64 (Jul-13 to Aug-23).

Once a range is defined, you can define other ranges from it. For example Plot_LCL =OFFSET(Plot_Date,0,6). This means Plot_LCL is the same range as Plot_Date, but shifted down zero rows and right 6 columns.

The named ranges shown on the Offset Calculations sheet are just for show. The actual ranges are defined with the name manager.

These named ranges are used in plotting the charts. For example, all the X-Axis were entered as ='Daily Charts'!Plot_Date. If you go back and check them you will see the workbook name!Plot_Date. That's because the named range is global in scope.

Table headers have to be something. These are "filler" headers - make them the same color as the background.

You could change the chart types to line charts instead of X-Y scatter plots (I've found they do not always scale well), they will look just the same and the trend lines will still work. Then you could set the Axis Type to Text.

Read the references I posted and you will be able to do it on your own .