Dynamic Chart using OFFSET

[deanjackson150]

Hi there,

So I'm trying to create a chart that updates itself dynamically and displays the last 7 weeks worth of data prior to the current date. I watched a handy tutorial online about creating named ranges out of the OFFSET functions that relate to the specific data column entries and have tried entering this into the Series for the Chart.

I'm aware that the reference must include the document name for this but excel still won't accept either that or the full file path (as output by the CELL,"filename" function).

Just wondering what I might be doing wrong, or if anybody can shed light on this. Thanks in advance!

 

[sijpie]

I don't use named ranges, but you could.

I have the following in sheet 'R TL'

Excel 2010
	V	W	X	Y	Z	AA	AB
4		NPT			Graph Ranges
57	Apr-11	0.78			May-11	0.21	12
58	May-11	0.21			Jun-11	0.95	11
59	Jun-11	0.95			Jul-11	1.00	10
60	Jul-11	1.00			Aug-11	1.29	9
61	Aug-11	1.29			Sep-11	0.52	8
62	Sep-11	0.52			Oct-11	1.01	7
63	Oct-11	1.01			Nov-11	0.53	6
64	Nov-11	0.53			Dec-11	1.46	5
65	Dec-11	1.46			Jan-12	4.16	4
66	Jan-12	4.16			Feb-12	3.28	3
67	Feb-12	3.28			Mar-12	2.84	2
68	Mar-12	2.84			Apr-12	0.18	1
69	Apr-12	0.18
70	May-12
71	Jun-12
72	Jul-12
73	Aug-12
R TL

Cell Formulas
Range	Formula
Z57	=OFFSET($V$5,COUNTA(W:W)-AB57-1,0,1,1)
AA57	=OFFSET($W$5,COUNTA(W:W)-AB57-1,0,1,1)

and then my graph range is as follows:
=SERIES(,'R TL'!$Z$57:$Z$68,'R TL'!$AA$57:$AA$68,1)

 

[sijpie]

Forgot to say, I add the monthly data to column W, the formulas in columns Z:AB then automatically take the last 12 entries, and the graph is linked to this range. Now and again, when i want to hide a few more lines, I just drag the graph range down a bit. (well I used to, as you can see the data is old... )

 

[deanjackson150]

Forgot to say, I add the monthly data to column W, the formulas in columns Z:AB then automatically take the last 12 entries, and the graph is linked to this range. Now and again, when i want to hide a few more lines, I just drag the graph range down a bit. (well I used to, as you can see the data is old... )

Thanks very much for your help and advice! Appreciate it.