IF statement combined with Chart function in Excel VBA -

i still new vba , far i've managed complete tasks online help, 1 far away knowledge.

example sheet here: http://s000.tinyupload.com/index.php?file_id=50923272994978624314

what want - if column contains of last dates of months (2015-01-31, 2015-02-28, 2015-03-31, 2015-04-30, 2015-05-31, 2015-06-30, 2015-07-31, 2015-08-31, 2015-09-30, 2015-10-31, 2015-11-30, 2015-12-31), select whichever of dates column has (select cells values precise), select corresponding cells in b column (for example, if a10 has 2015-04-30 in it, select b10 value well) , create chart (simple 2d clustered column) selected values (dates on horizontal axis, money amount on vertical axis).

idea have chart showing total amount of money @ end of each month (since values in b column cumulative) simple hand - find last day in month , see money value in corresponding b column, repeat last day in month in sheet , create chart. doing every day trying automatize (via macro).

i absolutely not use macro. instead make column of dates want capture. update values column chart automatically pick them hence why think macro useless here , formulas way go.

so lets start off making column of dates:

date        mtd revenue  2015 2015-03-09  € 2,855.270  2015-01-31 2015-03-10  € 3,534.550  2015-02-28 2015-03-11  € 3,791.760  2015-03-31 2015-03-12  € 4,024.680  2015-04-30 2015-03-15  € 4,182.020  2015-05-31 2015-03-16  € 4,317.610  2015-06-30 2015-03-17  € 4,439.900  2015-07-31 2015-03-18  € 4,585.070  2015-08-31 2015-03-19  € 4,798.900  2015-09-30 2015-03-20  € 4,903.830  2015-10-31 2015-03-21  € 4,959.710  2015-11-30 2015-03-23  € 5,203.690  2015-12-31 ...         ... 

if want have dates dynamic can use formula create "end of month dates" based on year. way have update year in column header change dates:

c ------------------------------------------------------------ 2015 =text(date(c$1,1,day(eomonth(date(c1,1,1),0))),"yyyy-mm-dd") =text(date(c$1,month(c2)+1,day(eomonth(c2,1))),"yyyy-mm-dd") =text(date(c$1,month(c3)+1,day(eomonth(c3,1))),"yyyy-mm-dd") =text(date(c$1,month(c4)+1,day(eomonth(c4,1))),"yyyy-mm-dd") =text(date(c$1,month(c5)+1,day(eomonth(c5,1))),"yyyy-mm-dd") =text(date(c$1,month(c6)+1,day(eomonth(c6,1))),"yyyy-mm-dd") =text(date(c$1,month(c7)+1,day(eomonth(c7,1))),"yyyy-mm-dd") =text(date(c$1,month(c8)+1,day(eomonth(c8,1))),"yyyy-mm-dd") =text(date(c$1,month(c9)+1,day(eomonth(c9,1))),"yyyy-mm-dd") =text(date(c$1,month(c10)+1,day(eomonth(c10,1))),"yyyy-mm-dd") =text(date(c$1,month(c11)+1,day(eomonth(c11,1))),"yyyy-mm-dd") =text(date(c$1,month(c12)+1,day(eomonth(c12,1))),"yyyy-mm-dd") 

(note: wrapped dates in text(..., "yyyy-mm-dd") because column values were. remove if dates , not text)

now can add index-match formula corresponding values:

a           b           c           d ---------------------------------------------------------------- date        mtd revenue  2015        mtd revenue 2015-03-09  € 2,855.270  2015-01-31  =index(b:b,match(c2,a:a,0)) 2015-03-10  € 3,534.550  2015-02-28  =index(b:b,match(c3,a:a,0)) 2015-03-11  € 3,791.760  2015-03-31  =index(b:b,match(c4,a:a,0)) 2015-03-12  € 4,024.680  2015-04-30  =index(b:b,match(c5,a:a,0)) 2015-03-15  € 4,182.020  2015-05-31  =index(b:b,match(c6,a:a,0)) 2015-03-16  € 4,317.610  2015-06-30  =index(b:b,match(c7,a:a,0)) 2015-03-17  € 4,439.900  2015-07-31  =index(b:b,match(c8,a:a,0)) 2015-03-18  € 4,585.070  2015-08-31  =index(b:b,match(c9,a:a,0)) 2015-03-19  € 4,798.900  2015-09-30  =index(b:b,match(c10,a:a,0)) 2015-03-20  € 4,903.830  2015-10-31  =index(b:b,match(c11,a:a,0)) 2015-03-21  € 4,959.710  2015-11-30  =index(b:b,match(c12,a:a,0)) 2015-03-23  € 5,203.690  2015-12-31  =index(b:b,match(c13,a:a,0)) ...         ...           

it should afterwards:

a           b            c           d ------------------------------------------------ date        mtd revenue  2015        mtd revenue 2015-03-09  € 2,855.270  2015-01-31  #n/a 2015-03-10  € 3,534.550  2015-02-28  #n/a 2015-03-11  € 3,791.760  2015-03-31  6,541.27 2015-03-12  € 4,024.680  2015-04-30  6,327.07 2015-03-15  € 4,182.020  2015-05-31  #n/a 2015-03-16  € 4,317.610  2015-06-30  #n/a 2015-03-17  € 4,439.900  2015-07-31  #n/a 2015-03-18  € 4,585.070  2015-08-31  #n/a 2015-03-19  € 4,798.900  2015-09-30  #n/a 2015-03-20  € 4,903.830  2015-10-31  #n/a 2015-03-21  € 4,959.710  2015-11-30  #n/a 2015-03-23  € 5,203.690  2015-12-31  #n/a ...         ... 

then can go ahead , make chart (2d clustered column... requested):

final note: technically since data dates, more correct use line graph on 2d clustered column.