adding new x & y values to charts automatically

[Guest]

Hi,

I have a number (approx. 30)of sales charts that each month need to change
the series for, so that the charts will include the new months sales.

Is there anyway other than to go into each individual graph, and change the
series - adding one addition column for the new month ... i.e from A1 to c1
for January to March to A1 to D1 for January to April.

If anyone can help - I'd really appreciate it, as this is just too time
consuming


Thanks

 

[Alex Delamain]

Take a look at Tushar Mehta's excellent site which has lots of info on
dynamic charts (among other things). His is based on the new data being
added to a new row but it shouldn't be too hard to change the offset()
terms to make it work for additional columns.

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

 

[Guest]

Thanks Alex,

The website seems really useful - I'm sure I'll be able to find the solution
to this problem there

Rgds
Daniel

 

[Alex Delamain]

This might help further:

Create a named range using Insert Name Define (e.g. call it "Numcols")

=Offset($a$1,0,0,1,counta($1:$1))

Then create a further range for each row of data

=offset(numcols,rownumber,0)

where row number is the number of rows below row 1 for the dat
concerned (ie if you want to plot row 2 then rownumber =1.)

Then plot your charts with the data series as worksheet1!yourdatanam
and x axis as worksheet1!numcols

Every time you add a new column to row 1 then all graphs will updat
and add that column