Scrolling through date based data on charts

**TommyG85** · 05-19-2016, 11:35 AM

Hi!

I have a chart that looks at 9 series' of data over three months (see picture). The table it takes the information from goes through to December.

I was wondering if there was a way to 'scroll' through the graph, three months at a time. i.e. May-June-July, June-July-August, July-August-September etc etc..

Is this possible!?

Graph Scrolling.PNG

Thanks!
Tom

**dflak** · 05-19-2016, 01:00 PM

Please attach a sample workbook. It sounds like you can do what you want by creating named dynamic ranges and setting them up as the data source for the chart.

Here are some articles that can help:
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

**José Augusto** · 05-19-2016, 01:03 PM

Hi
I used VBA code in Graph sheet to solve your problem.
You can place the cursor in cell A1 to obtain the graph of the first three months. After that you can move on line one and get successive graphics as you intended.
See the file Graph_Tri.xlsm

**dflak** · 05-19-2016, 01:39 PM

Here is the approach using named ranges. The offset command for P_Month gets the selected and following two months. It uses MIN to keep the three month range from going past the end of the data.
P_Month =OFFSET(Graph!$A$1,0,MIN(MATCH(Graph!$C$12,Graph!$A$1:$L$1,0),10)-1,1,3)
P_S_1 =OFFSET(P_Month,1,0)
P_S_2 =OFFSET(P_Month,2,0)
P_S_3 =OFFSET(P_Month,3,0)
P_S_4 =OFFSET(P_Month,4,0)
P_S_5 =OFFSET(P_Month,5,0)
P_S_6 =OFFSET(P_Month,6,0)
P_S_7 =OFFSET(P_Month,7,0)
P_S_8 =OFFSET(P_Month,8,0)