How do I split daily time series data into seasonality grid by years

Hey All,

I have daily time series data from 2006-today : Column A = daily dates, Column B = values

Im trying to build a tool that is able to plot a seasonality chart for the data I have: i.e. x axis = Jan, Feb .... Dec; y axis = values; Each series/line = a different year within the data range.

I want to eventually be able to build a macro such that whenever i update the hardcoded data in column A and B, I run a Macro, that sorts the data and charts it, without the use of pivot tables

I've attached the worksheet so it is easier to understand.
Seasonality tool.xls


What I've already tried:
1. A vlookup method using the following formula: VLOOKUP(DATE(YEAR(H$1),MONTH($G2),DAY($G2)),$A$2:$B$1956,2,)

Problems Im having:
1. Having the grid pick up the values - i just get N/As
2. Having the years column dynamic relative to the data i.e cells H1-O1 able to change with the data series in column A.

Your help would be much appreciated

Thanks

Thank you Martin, i can now get the values. Much appreciated

And how can i go about making the years headings dynamic relative to the data i.e cells H1-O1 able to change with the data series in column A??
i.e. if i have data between 2003-2010, the headings automatically pickup 2003, 2004,...,2010

And in turn the x-year average is also dynamic, taking the average for the dates I have i.e. if i have 2008-2013 data, it automatically adjusts to take the average over those years.



I wont ever need to use more than 10 years of data

Originally Posted by martindwilson

g1:o1 are not dates just values so
=VLOOKUP(DATE(H$1,MONTH($G2),DAY($G2)),$A$2:$B$1956,2,FALSE)

Indeed, sorry missed the last line in your first response.

Thank you very much for your help

Regards

Hello. Is this formula also applicable to MS Office 2007? I followed the answer and applied it to the worksheet [Seasonality tool.xls], but I'm getting '#N/A'.