Macro to update graph source data

[antony moseley]

Hi,

I would like to write a macro to be able to update the source data for a graph every week.
The data is under series tab, rather than data range.
Series header NET
this week the values are:
=Weekly!$AB$5:$AB$16
next week i will want it to show
=Weekly!$AB$5:$AB$17

and the category X axis labels will need to change from =Weekly!$A$5:$A$16 to =Weekly!$A$5:$A$17.

Any ideas?

Thanks

Antony

[rwgrietveld]

Please look into the excel file. You can use DYNAMIC ranges

Xdata: =OFFSET(Sheet1!$AB$5,0,0,COUNTA(Sheet1!$AB$5:$AB$100),1)
Ydata: =OFFSET(Xdata,0,1,,)

Graph: =SERIES("Graph",dyn_graph.xls!Xdata,dyn_graph.xls!Ydata,1)

Please add your own values into AB17, AB18, ...

[antony moseley]

Hi,

Where do i add my version of
Xdata: =OFFSET(Sheet1!$AB$5,0,0,COUNTA(Sheet1!$AB$5:$AB$100),1)
Ydata: =OFFSET(Xdata,0,1,,)

Graph: =SERIES("Graph",dyn_graph.xls!Xdata,dyn_graph.xls!Ydata,1)

Thanks

Antony