Dabbling with dynamic charts

[AlexH]

I have a small problem with some data and a chart for project management data. It goes a bit like this.

I have 2 sets of data. Planned spends (based on people and hours) and actual spends (again people and hours). The planned is for a full year and the actual is updated weekly based on timesheets.

I have 1 graph that displays cumulative planned spends and cumulative actuals. I would like this graph to update the actual spends as the date changes. i.e. when the data is inputted at the end of the week.

The data is arranged as follows

Week ending (on a Friday) - Cells J5:BI5
Actual cumulative spends - Cells J101:BI101

Some defined names are
WeekEnding - defined as =K109+(5-WEEKDAY(K109,2)) where K109 is today()
WeekNumber - defined as a number between 1-52 based on the date and linked to Week Ending.

In the olden days I created a chart and changed the range manually every time I added new data so the graph only displayed the current weekly data. I'd like this to be done automatically based on the date (the last week ending date).

Hope this makes sense and I'm sure its not too tricky. I just need some pointer in the right direction for how to put the data into the graph.

Thanks

Alex

 

[Luke M]

I'd suggest either starting here:
http://peltiertech.com/Excel/Charts/DynamicLast12.html

or posting a sample workbook so we have a better feel for the layout of your data. But Jon Peltier has several wonderful articles on charts, so I think you should be able to get it from there.

 

[AlexH]

Thanks Luke, I was just working on a sample data book to make things a bit easier to understand and came about a solution from a colleague.

he suggested a logical test to check the date and return a n/a if that date had not passed. Attached (hopefully) is our attempt. The cell references have changed but the inference is still the same.