Chart automatically updates with current weeks data only

[toadmussen]

Hi all,

I have a data set and I want to have a line chart that automatically graphs only the current weeks data. This data set will contain a whole months data (infact, the currents weeks data, will at the end/start of the month span over two tabs, as the data is setup month by month on different tabs). Throughout the week I will be adding to the data set. Can I do this as a pivot table (and say graph it based on week number), or by a dynamic chart, or ....? I have tried both but can't get them to work.

I also need to colour sections of the graph say green/amber/red, to show the ranges that are good, ok, and poor results.

I have attached some example data.

Any help would be appreciated.


Thanks,
toadmussen