Change graphs automatically when a row is inserted

I want the 2 graphs in "Graph" worksheet to change automatically when a row is inserted in "Data" worksheet . Every time i have to change the graph manually to contain the latest 20 days value. I want some offset or something which can be put in the range provided below to do the work.


Chart Data Range =Data!$A$1:$A$22,Data!$F$1:$F$2,Data!$L$1:$L$20

Legend entries (Series) -
Series Name
=Data!$F$1 ( this will remain constant everyday as this is a header field)

Series Values
=Data!$F$2:$F$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then
it should automatically change to =Data!$F$3:$F$23)

Legend entries (Series) -
Series Name
=Data!$L$1 ( this will remain constant everyday as this is a header field)

Series Values
=Data!$L$2:$L$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then
it should automatically change to =Data!$L$3:$L$23)

Horizontal (Category) axis series -
Axis Label Range
=Data!$A$2:$A$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then
it should automatically change to =Data!$A$3:$A$23)

Sheet attached. Please help me as i am a newbie.

Devicess.xlsx

Hi, aakhan2011,

the answer here would be the same I posted at http://www.excelforum.com/excel-prog...ml#post3708737 without getting any response from your side on what isnīt working on that thread.

Ciao,
Holger

Try this. I have converted your data to a Table, as a new row is added, the formulae will automatically copy down. Your column headers are a bit of a nightmare - way too long!!

There were a large number of entries in your calciulation columsn which were NOT calculations, but which were manually typed in. They are all now correctly formatted as calculations.

Anyway, in columns o - r I have created a list of the most recent 20 entries. The graphs automatically update as you add data. I have done this only for one graph. Try and set the other one up yourself & let me know how you get on.

In the finished product, you can either hide the helper table, or (as I tend to do) put the graph on top fo the helper table.

Hi, that's because I added two extra rows to test it out. I didn't delete them. Is this more like what you expected? You'll see that the first row of the graph's data is an error (#N/A). This is because, so far, you only have 19 points in your dataset. It'll disappear when you add another day and thereafter the graph will display only the latest 20.

If this answers your Q, please mark the thread as closed and (ideally) say thanks by clicking the ADD REPUTATION button at the foot of my post.