Updating Excel chart

[Bigbusiness]

I have made a candlestick chart using excel but every time I add data I have to edit the source data to see the new candlestick. As there are a few moving averages, this involves updating 13 series of data!

Is there an easier way of updating the chart after adding new data?

 

[sidinuk]

Extend the series used for the source data. Ie if your data is in A1:A50 then make the source data A1:A5000. The chart should only show the cells that actually have data in them so as you add data to A51, A52 etc the chart will automatically pick it up.

hope that helps!

Tim

 

[bonsai]

BB
write your formula with a test of the data in it.
for example
if ab123>0 then average else @error

you can then copy your formula down beyond the existing data
and when you add new data, the formula will calc the average you want.

the construct of the formula will be different for excel but
you should get the gist ?

 

[bonsai]

oops
sorry sid. didnt see you post till I finished mine

and I use Lotus anyway.

 

[Bigbusiness]

Thanks for the quick replies. I tried Tim's method but the chart rescaled to fit 5000 data entries.

A search through google groups provided this method. I will give it a go when I can find some time.

http://www.geocities.com/jonpeltier/Excel/Charts/DynamicColumnChart1.html

 

[sidinuk]

further to my method above. This only seems to work if you use dates as the category x axis labels.