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...

 

[skinnea]

Anyone...?

 

[Domenic]

Try the following...

First, define the following references...Insert > Name > Define...

Name: NumWeeks
Refers to:
=Sheet1!$Q$2

Name: Dates
Refers to:
=OFFSET(Sheet1!$B$1,MATCH("*",Sheet1!$B:$B,-1)-1,0,-MIN(NumWeeks,MATCH("*",Sheet1!$B:$B,-1)-1))

Name: Total
Refers to:
=OFFSET(Dates,0,1)

Name: BAU
Refers to:
=OFFSET(Dates,0,2)

Name: Greater6Days
Refers to:
=OFFSET(Dates,0,3)

...and so on.

Once you have all of the references defined, you can start creating your charts. For your first chart...

1) Switch to Sheet2

2) Insert > Chart > Finish > OK

3) Enter the formulas for whichever and however many data series/columns you wish. For example:

=SERIES(Sheet1!$E$1,'Dynamic Chart.xls'!Dates,'Dynamic Chart.xls'!Greater6Days,1)

=SERIES(Sheet1!$F$1,'Dynamic Chart.xls'!Dates,'Dynamic Chart.xls'!Less6Days,2)

=SERIES(Sheet1!$G$1,'Dynamic Chart.xls'!Dates,'Dynamic Chart.xls'!Cancelled,3)

...and continue until you're done. You can then change your chart type, format, etc.

Repeat the process for your other charts, again, selecting whichever data series/columns you wish to include.

Cell Q3 on Sheet1 determines how many week's worth of data to display. Change this number to the desired weeks.

If you'd like I can email you a sample file. Let me know if you so wish.

Hope this helps!

 

[skinnea]

Thanks Domenic, that looks great and I think ( ) I understand how it's working/what it's doing.

However, where to enter the =SERIES bits is stumping me; when I go to the inserted blank chart on Sheet2 and right click, select Source data and Series, I get error messages when I put the forumlas in any of the 3 boxes (Name, Values or Axis labels). The error message says I have en error in the formula...

If you would be as kind as to email me the file you have that works, I'd be extremely grateful; would it show me how to make one of the graphs a stacked 100% bar with data from columns F thru' K in each week? I'm a bit hazy on making it do that too...

 

[Domenic]

Once you do the 'Insert > Chart > Finish/Ok' bit, it should give you a blank chart window. At this point, all you have to do is begin entering the formula. The formula will automatically be entered in the formula bar. When you're finished entering your first formula, enter the next one, and so on.

Alternatively, you could go through the steps in the Chart Wizard...

1) Insert > Chart

2) Choose Chart Type

3) Choose Data Range tab

4) Series in: Column

5) Choose Series tab

6) Click Add to add the first series

7) Name: =Sheet1!$F$1

8) Values: =Dynamic Chart.xls!Less6Days

9) Category Labels: =Dynamic Chart.xls!Dates

10) Click Add

...and so on.

I'll be happy to send you a sample file. Send me your email address either through Private Message or Email.

 

[Domenic]

8) Values: =Dynamic Chart.xls!Less6Days

9) Category Labels: =Dynamic Chart.xls!Dates

That should be...

8) Values: ='Dynamic Chart.xls'!Less6Days

9) Category Labels: ='Dynamic Chart.xls'!Dates

 

[skinnea]

I've sent you a PM, as I am still getting error messages (even with the corrected details.

 

[Domenic]

You'll need to replace the reference to the workbook ('Dynamic Chart.xls') with the name of your own workbook. Or you can replace it with 'Sheet1'.

Also, I've emailed you a sample. Let me know if you don't receive it.

 

[skinnea]

After some other moments of frustration that I can now put down to my own stupidity , this works like a charm.

Many, many thanks Domenic!

 

[skinnea]

After having great success with the above, I need to revisit ad apply it to a chart where the dates are the column headings and the data is in rows accordingly.

My question therefore is, how do I change the following Name definition to run horizontally rather than vertically...? Specifically the last 3 cells with text.

Name: Dates
Refers to:
=OFFSET(Sheet1!$B$1,MATCH("*",Sheet1!$B:$B,-1)-1,0,-MIN(NumWeeks,MATCH("*",Sheet1!$B:$B,-1)-1))