Rolling Date Axis Graph from a Complex Data Chart

[skinnea]

Hi. I have a data chart that shows a number of sets of data, as the small extract below shows;

AS_Revised IBM Weekly Change Stats.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ReportingWeek	Total	BAU	>6daysnotice	<6daysnotice	Cancelled	Rejected	NotUsed	Failed	OnHold	Successful	Unsuccessful	Successful	Failed
2	05/11-11/11	33	6	29	4	5	1	0	12	0	11	18	11	12
3	12/11-18/11	44	0	36	8	19	2	0	4	0	16	25	16	4
4	19/11-25/11	36	10	28	8	5	0	0	1	0	31	6	31	1
5	26/11-02/12	58	8	37	21	5	20	0	3	0	25	28	25	3
6	03/12-09/12	24	11	12	12	8	2	0	3	0	8	13	8	3
Example Graphs

I am pulling 5 graphs (100% stacked ones) out of that, showing various comparisons against adjacent colums (hence the duplication at the end...).

I want to be able to have these graphs automatically show just the last X number of weeks each time I open the spreadhseet and enter data against the current week.

As you'll see, the chart above pulls its data from another sheet in the workbook - and to top it off, I want the 5 graphs to be on a different sheet to the chart above.

Does that make sense? I am struggling to understand how I can achieve my desired results, but suspect it has something to do with OFFSET and TODAY forumlas?

Any help would be much appreciated...

 

[Domenic]

Try...

=OFFSET(Sheet1!$B$1,0,MATCH("*",Sheet1!$B$1:$O$1,-1)-1,1,-MIN(NumWeeks,MATCH("*",Sheet1!$B$1:$O$1,-1)-1))

Hope this helps!