Extract date and chart

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
ABCDEFGH
7Midlands History
8Date2004 Figure2005 Figure2006 FigureMonthTarget
904/01/0559120Jan
1006/12/0451120Feb
1122/11/0451120Mar
1208/11/0451120Apr
1325/10/0452120May
1418/10/0452120Jun45
1511/10/0452120Jul45
1604/10/0451100Aug45
1720/09/0451100Sep45
1813/09/0451100Oct50
1906/09/0450100Nov50
2031/08/045090Dec50
2109/08/045090
2202/08/044990
2326/07/044980
2419/07/044880
2512/07/044680
2628/06/044440
2721/06/04449.20
2814/06/044490
2907/06/044270
30
Midlands
[/quote]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDEFGHIJK
7MidlandsHistory
8Date2004Figure2005Figure2006FigureMonthTarget2004Figure2005Figure2006Figure
92005-01-0459120Jan59120
102004-12-0651120Feb4570
112004-11-2251120Mar4560
122004-11-0451120Apr
132004-09-2252120May
142004-09-1852120Jun45
152004-09-1452120Jul45
Sheet4
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
BCDEFGHIJKLMN
4
5MonthJanFebMarAprMayJunJulAugSepOctNovDec
6Workload05#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
7Target05
8Workload06
9Target06
10MidlandsHistory
11Date2005Figure2006Figure2007Figure
1204/01/0559097000129817210
1306/12/0451643135127279980
1422/11/0451643135127279980
1508/11/0451643135127279980
1625/10/0451591411120134740
1718/10/0451591411120134740
1811/10/0451591411120134740
1904/10/0451218915103882180
Midlands
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
 

Forum statistics

Threads
1,148,530
Messages
5,747,238
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top