Automatic data update

[Tommy Keraf]

Dear All,
I want to know whether there is such function in Ms Excel?..
For example, I have some data series consisting 100 data..and every month I
will add 1 data into the series..
And I want to create a chart showing the 10 latest data... in this regard,
every time I add a data, automatically (or by clicking some button)...the
chart will updating the data by add the new one and remove the oldest one,...
so the chart still consist the 10 latest data...
what i've been doing right now is only by dragging the data source area....
and since i will deal with more than 20 charts, this action quite time
consuming (since I put all charts in 1 sheet and the other sheets consist
of data for each charts)..

I am sorry that my english is not good.. but I hope all of you can
understand what I mean.. and help me with this issue..

thanks a lot..

 

[Andy Pope]

Hi,

Have a read of the information on Dynamic charts.
http://peltiertech.com/Excel/Charts/Dynamics.html

It will take some setting up for your 20 charts but once set up it
should automatically update each time.

Cheers
Andy

 

[Tommy Keraf]

Dear Mr. Pope,

Thanks a lot for your help. I have followed the instruction and it works ..

I want to ask your help once more.. about the command "..
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) .."

how to modify this .. if I use two columns for period ... (in column A :
Year: and column B; month)...

col A ColB
2000 Jan
Feb
Mar
....
....
2006 Jan
Feb
Mar

So the year only appear in the same row with January.
I've been trying to modify the command, but it does not work well..


Thank you

 

[Andy Pope]

Hi,

Assuming your data is in A1:C75 use these two named ranges to get most
recent 12 months data.

CHTLABEL:
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$B:$B)-MIN(12,COUNTA(Sheet1!$B:$B)),0,MIN(12,COUNTA(Sheet1!$B:$B)),2)

CHTDAT:
=OFFSET(ChtLabel,0,2,,1)

You may also need to use formula in order to display the year value in
the correct cell such that it will be displayed in the chart.

Cheers
Andy