Question about OFFSET limits and Dynamic Plotting

[rwmill9716]

I have created a dynamic chart that plots Col E (PRIs) against Col D (Date) as new data become available. It uses two named statements in the charting program:

=OFFSET('47 Tank Ppk'!Target_Metric,0,-1) for X and
=OFFSET('47 Tank Ppk'!$E$8,0,0,COUNTIF('47 Tank Ppk'!$D:$D,">0")-0,1) for Y (this is named Target_Metric)

That works fine. As new data is added to Cols D and E, the chart updates without me having to open it and adjust row numbers.

I want to add a second variable to plot (Ppk) to this chart: Col I against Col D, but I cannot create OFFSET statements like the above that work. Ppk is a running calculation
based on the prior 30 PRI data. That's why this column begins on row 37.

Any help would be appreciated.

Excel 2010
	D	E	F	G	H	I	J	K
6	Date	PRI
7
8	8/1/2013 6:30:00	1.8
9	8/1/2013 18:30:00	1.9	0.1
10	8/2/2013 6:00:00	1.6	0.3
11	8/2/2013 16:55:45	1.7	0.1
12	8/3/2013 4:52:48	1.8	0.1
13	8/3/2013 18:30:00	1.8	0
14	8/4/2013 5:00:54	1.8	0
15	8/4/2013 16:54:12	1.8	0
16	8/5/2013 6:00:00	1.7	0.1
17	8/5/2013 18:00:00	1.7	0
18	8/6/2013 6:30:00	2.1	0.4
19	8/6/2013 9:01:40	1.8	0.3
20	8/6/2013 18:00:00	1.8	0
21	8/7/2013 6:15:00	1.7	0.1
22	8/7/2013 16:48:18	2.2	0.5
23	8/7/2013 22:18:27	2.1	0.1
24	8/8/2013 3:46:26	2.5	0.4
25	8/8/2013 4:57:17	2.1	0.4
26	8/8/2013 8:44:13	2.3	0.2
27	8/8/2013 13:46:48	2.1	0.2
28	8/8/2013 17:03:22	2.1	0
29	8/8/2013 23:53:12	1.9	0.2
30	8/9/2013 6:02:00	2.1	0.2
31	8/9/2013 14:29:10	1.9	0.2
32	8/9/2013 17:00:55	1.8	0.1
33	8/10/2013 6:00:00	2	0.2			Ppk = (2.5 - ) / 3s
34	8/10/2013 18:30:00	1.8	0.2
35	8/11/2013 6:00:00	1.9	0.1		s	Ppk
36	8/11/2013 18:30:00	2	0.1
37	8/12/2013 5:15:26	1.9	0.1	1.92	0.20	0.95
38	8/12/2013 17:21:07	1.8	0.1	1.92	0.20	0.95
39	8/13/2013 0:01:00	1.7	0.1	1.92	0.21	0.94
40	8/13/2013 6:30:00	1.7	0	1.92	0.20	0.96
41	8/13/2013 12:08:15	1.6	0.1	1.92	0.21	0.94
42	8/13/2013 17:14:58	1.5	0.1	1.91	0.22	0.90
47 Tank Ppk

 

[Jerry Sullivan]

Do you want a want a dynamic range for the cells in Column I that align with the cells in Target_Metric?
If so, one way would be...
=OFFSET('47 Tank Ppk'!Target_Metric,0,4)

If you are going to use this as the data source for a chart series, you would probably want to populate the cells in $I$8:$I$36 with #N/A values.

 

[rwmill9716]

Thanks, Jerry. Your suggestion works fine.

ric