Extending Series with VBA?

[Helmut Glänzer]

I have an XL sheet which gets extended periodically (new colums inserted).
Now I want the corresponding chart to be extended automatically (add one
colum to the range). How can this be done?

 

[Frank Kabel]

Hi Helmut
have a look at the following web-sites for dynamic chart examples:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

HTH
Frank

 

[Helmut Glänzer]

Hi Frank,
thx for the hint, works great! Now I have one additional challenge with one
of the charts. The data fields that make up a series are not adjacent to one
another. Rather a series is comprised of say fields A1, A5, A10; When lines
are added to the table the series should be automatically extended by field
A15 (but not A11-A14). Is there also a way to define a name for this or what
can be done to automate the chart in this case?

Best, Helmut

 

[Frank Kabel]

Hi Helmut
I would create a helper column which stores only your relevant charting
data. In your example add the following formula in B1:
=OFFSET($A$1,(ROW()-1)*4,0) -> copy down
Note: Your example series may not be correct. between A1 and A5 are 3
rows, between A5 and A10 4 rows. The above formula works for 3 rows in
between. So it would gather the series A1, A5, A9, A13

HTH
Frank