Extract date and chart

[staticbob]

Guys,

I need to create a line chart from the data below. My users enter figures in weekly. I need to extract the latest data for the month, and plot it against the target for that month.

So for each month, there will be 4 entries. I only want to plot the latest date in each month.

Any ideas ???
Bob

Midlands2005.xls
	A	B	C	D	E	F	G	H
7	Midlands History
8	Date	2004 Figure	2005 Figure	2006 Figure		Month	Target
9	04/01/05	59	12	0		Jan
10	06/12/04	51	12	0		Feb
11	22/11/04	51	12	0		Mar
12	08/11/04	51	12	0		Apr
13	25/10/04	52	12	0		May
14	18/10/04	52	12	0		Jun	45
15	11/10/04	52	12	0		Jul	45
16	04/10/04	51	10	0		Aug	45
17	20/09/04	51	10	0		Sep	45
18	13/09/04	51	10	0		Oct	50
19	06/09/04	50	10	0		Nov	50
20	31/08/04	50	9	0		Dec	50
21	09/08/04	50	9	0
22	02/08/04	49	9	0
23	26/07/04	49	8	0
24	19/07/04	48	8	0
25	12/07/04	46	8	0
26	28/06/04	44	4	0
27	21/06/04	44	9.2	0
28	14/06/04	44	9	0
29	07/06/04	42	7	0
30
Midlands

[/quote]

 

[fairwinds]

Hi,

You could transfer the last entry for each month into the source table for your chart with this formula:


=INDEX(B$9:B$29,MATCH(MAX(IF(TEXT($A$9:$A$29,"MMM")=$F9,$A$9:$A$29)),$A$9:$A$29,0))

Entered with Ctrl + shift + Enter in H9 and dragged right / down.

Book1
	A	B	C	D	E	F	G	H	I	J	K
7	MidlandsHistory
8	Date	2004Figure	2005Figure	2006Figure		Month	Target	2004Figure	2005Figure	2006Figure
9	2005-01-04	59	12	0		Jan		59	12	0
10	2004-12-06	51	12	0		Feb		45	7	0
11	2004-11-22	51	12	0		Mar		45	6	0
12	2004-11-04	51	12	0		Apr
13	2004-09-22	52	12	0		May
14	2004-09-18	52	12	0		Jun	45
15	2004-09-14	52	12	0		Jul	45
Sheet4

 

[staticbob]

Thanks FW,

Can you see why this isn't working, my layout is slightly different but I think I have changed the formula correctly ???

Bob

Midlands2005.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N
4
5	Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	Workload05	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
7	Target05
8	Workload06
9	Target06
10										MidlandsHistory
11										Date	2005Figure	2006Figure	2007Figure
12										04/01/05	59097000	12981721	0
13										06/12/04	51643135	12727998	0
14										22/11/04	51643135	12727998	0
15										08/11/04	51643135	12727998	0
16										25/10/04	51591411	12013474	0
17										18/10/04	51591411	12013474	0
18										11/10/04	51591411	12013474	0
19										04/10/04	51218915	10388218	0
Midlands

 

[staticbob]

Sorry FW,

I've sorted it. I actually had the Months in row 5 entered as dates, and formatted as mmm. I have now entered these as text.

Thanks
Bob