VBA macro to update the graph from new set of data

[pankaj99in]

Hi Friends,

I have been been able to use "OFFSET" function to automate the graph update but i am looking for VBA macro which i can used across excel to automate the graph .While working with offset i found it is time consuming and not very much optimized if there are many factors involved as you need to create "offset" and assign name range for each of them .Do we have VBA way of doing that ?

In other words, i am looking for VBA to do the same as done with offset below :

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

 

[Hui]

The best way is to simply have a table of all the data
Then setup a base Named Formula for Typically the X Axis
this will include an offset for Dates/weeks etc
then use other Named Formula for the Series Values (Y values)
this will simply be offset from the X Axis Named Formula

Si in your example the X Values "Weeks" maybe a named Formula like
Weeks: =OFFSET('RGVN App Data Table'!C2,x,,4,1)
where x is the starting week no or offset cell eg 2

Then for the Y values
Backlog_App_Sup: =Offset(Weeks,,5)
because the Backlog_App_Support column is 5 columns away from the weeks

Using this technique 1 cell can change all the charts