Create a Dynamic Line Chart for last 4 weeks of data

[Wullay]

Hello,
I have an excel s/sheet which gets populated every day (Monday-Friday) with performance data and then each week an average gets calculated as per the below.

From this each week, I need to produce a line chart for each colour's data which shows the last 4 weeks only.

Due to the weekly average getting in the way as such, I have set up a new tab sheet which has a table in it with the same data as above but for Monday-Friday only and have then set up a Dynamic rolling line chart to only show the last 4 weeks using Define Names etc .

However, this still means I have to manually update the second sheet table each week with the data as I cannot find a way to automatically get the data which gets entered daily into sheet 1, to update sheet 2. At the moment I have a formula of =Sheet1!C24 for example in cell B3 above but I can't find a way to fill that down the column as I'm going across in one row in Sheet 1. I also have the issue again with the weekly average getting in the way.

My questions are:
1. Using the top table - is there a way to get excel to produce a line chart just using data from Monday-Friday in Sheet 1 and then get this chart to only ever display the last 4 weeks, without creating an additional table.
2. If not, how do I get sheet 2 to automatically populate from sheet 1 and do this from there?

I basically want a chart which gets produced automatically from the data being input daily to show the past 4 weeks data, without having to do any additional work.

Thanks in advance.

 

[StephenCrump]

Welcome to the Forum!

This is why it's a good idea to keep the data entry area (i.e. daily results) separate from the results area (weekly averages).

If you have access to Excel's FILTER function, you can graph directly from your table:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	TOTAL	Mon 9 Dec	Tue 10 Dec	Wed 11 Dec	Thu 12 Dec	Fri 13 Dec	TOTAL	Mon 16 Dec	Tue 17 Dec	Wed 18 Dec	Thu 19 Dec	Fri 20 Dec	TOTAL	Mon 23 Dec	Tue 24 Dec	Wed 25 Dec	Thu 26 Dec	Fri 27 Dec
3		10.0	10.0	9.3	11.3	8.7		7.0	9.4	10.1	9.2	11.4		13.9	14.9	15.9	16.8	15.9
4
5	End date	Wed 25 Dec
6	NoWeeks	2
7
Sheet1

Names:
EndDate: =Sheet1!$B$5
NoWeeks: =Sheet1!$B$6
MyDates: =LET(d,Sheet1!$B$2:$Z$2,FILTER(d,(d<=EndDate)*(d>EndDate-NoWeeks*7)))
MyNumbers: =LET(d,Sheet1!$B$2:$Z$2,n,Sheet1!$B$3:$Z$3,FILTER(n,(d<=EndDate)*(d>EndDate-NoWeeks*7)))

Then in the graph, define

- the "Amount" series values as: =Sheet1!MyNumbers, and
- the Axis labels as: =Sheet1!MyDates

the resulting (dynamic) graph looks like this for the data fields shown:

 

[StephenCrump]

With an older version of Excel, you could set up a table, filter the blank amounts, and graph the table results --> same graph as above.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	TOTAL	Mon 9 Dec	Tue 10 Dec	Wed 11 Dec	Thu 12 Dec	Fri 13 Dec	TOTAL	Mon 16 Dec	Tue 17 Dec	Wed 18 Dec	Thu 19 Dec	Fri 20 Dec	TOTAL	Mon 23 Dec	Tue 24 Dec	Wed 25 Dec	Thu 26 Dec	Fri 27 Dec
3		10.0	10.0	9.3	11.3	8.7		7.0	9.4	10.1	9.2	11.4		13.9	14.9	15.9	16.8	15.9
4
5	End date	Wed 25 Dec
6	NoWeeks	2
7
8	StartCol	16
9	EndCol	5
10
11
12	Day	Date	Amount
13	1	Thu 12 Dec	11.3
14	2	Fri 13 Dec	8.7
16	4	Mon 16 Dec	7.0
17	5	Tue 17 Dec	9.4
18	6	Wed 18 Dec	10.1
19	7	Thu 19 Dec	9.2
20	8	Fri 20 Dec	11.4
22	10	Mon 23 Dec	13.9
23	11	Tue 24 Dec	14.9
24	12	Wed 25 Dec	15.9
25
Sheet1

Cell Formulas
Range	Formula
B8	B8	=MATCH(B5,A2:R2,)
B9	B9	=B8-B6*6+1
B13:B14,B16:B20,B22:B24	B13	=INDEX(A$2:R$2,B$9+A13-1)
C13:C14,C16:C20,C22:C24	C13	=IF(INDEX(A$3:R$3,B$9+A13-1)="","",INDEX(A$3:R$3,B$9+A13-1))

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet1!$B$5	B8
NoWeeks	=Sheet1!$B$6	B9