Charts in Excel

[pfenton]

I need to create a chart in Excel(not that difficult, I know!) which will graph weekly yield totals.
I've set up several spreadsheets linking daily outputs to Pass/Fail totals, from which a graph is created.
However (the problem!), the boss would like the graph to automatically change on the Monday of every week!
(eg) The graph will show the first 5 weeks of the year (weeks 1-5).
On the Monday of the 7th week, the boss would like to see a graph of weeks 2-6, on the 8th week a graph of 3-7 etc. etc.

Does anyone know of a way to deal with this?
Is there some kind of macro to do this?

Many thanks

PF

Go nairigh an bothar leat.

 

[dsi]

You could write a small application to handle this. Assuming your data is sorted by date, iTop represents the first item (row) that you want to display and iBot represents the last item(row) that you want to display in the chart. The Chart Object Name is Chart1, and the plot data is located on a sheet called Sheet1 and is in columns A through D:

'Redefine the Source Data for the Chart (range)
sTmp = "A" & iTop & ":A" & iBot
sTmp = sTmp & ",D" & iTop & ":D" & iBot

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range _
                          (sTmp), PlotBy:=xlColumns

Hope this helps! DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.