Converting static charts to dynamic charts

[nikhil83]

Hi,

My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers

Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated. please help

<tbody>
</tbody>

 

[Asala42]

If you want a chart to update automatically as you add new data, the standard approach is to use named ranges for your X-label and each of your y-series. You'll want to identify a field that always has data.

For example create the named ranges:
MyXRange = OFFSET(Sheet!$C$2,0,0,COUNTA(Sheet!$C$2:$C$1000),1)
MyCostRange = OFFSET(Sheet!$A$2,0,0,COUNTA(Sheet!$C$2:$C$1000),1)

These named ranges resent Column C data and Column A data respectively, but limits the range from C2 through the count of entries in column C.

Then you can insert these ranges into your series formulas on your charts. For example instead of:
=SERIES(,Sheet1!$C$2:$C$14,Sheet1!$A$2:$A$14,1)

use:
=SERIES(,MyFile!MyXRange,MyCostRange!yrng,1)

 

[nikhil83]

please explain in a way that is specific to my case.Since my data is already in table format,i think i don't need to name the range separately