Excel Charting 4

[moontho]

I'm sure this is simple. I have a simple line chart that needs to be updated weekly when extra data is added to the end of the source data. Is there away to get the chart to pick this automatically without having to reapply the source data?

 

[xlbo]

Easiest way is to use a "dynamic named range"
To set this up, you will need a sheet which contains the chart data, starting in A1 - lets call it Chartdata. then goto Insert>Name>Define and add a name (dyChart1 for example)
Then enter this instead of a cell ref:
=OFFSET(ChartData!$A$1,0,0,COUNTA(ChartData!$C:$C),1)

This will create a named range that expands with the data it holds.

You will need to create 1 of these for each of the series you are plotting. to reference them in a chart, use:

='Workbookname.xls'!dyChart1
as the source data

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Pennanti]

Thanks for that information, expanding chart data is something I deal with all the time.
Star for you.

 

[moontho]

Thanks Geoff. Very helpfull.

Though I found it a little hard to get multiple line charts to update automatically when extra data was added, but i got around this by creating a DNR for each column of data and assigning it to each individual line in the formula bar itself!!! (if that makes sense). i.e., for line one
=SERIES(Sheet1!$B$1,Sheet1!dyDate,Sheet1!dyData,1)
then for the second line
=SERIES(Sheet1!$C$1,Sheet1!dyDate,Book1.xls!dyMoreData,2)

Worked a treat.

A star

 

[xlbo]

Thx moontho - just a small point but I did mention that in my 1st post:
"You will need to create 1 of these for each of the series you are plotting."


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[moontho]

I missed that!! Thanks Geoff. I'm the star of the office now!!

 

[DeLaMartre]

Great Stuff!

Thanks for your brilliance, Geoff. Another Star for you!

-Bob in California

 

[moontho]

One last think!! is there a way to make the DNR count along the row rather than down the column?

Many thanks

Thom

 

[xlbo]

yup:
You can make it go just along 1 column:
=OFFSET(ChartData!$A$1,0,0,COUNTA(ChartData!$C:$C),1)

just along a row:
=OFFSET(ChartData!$A$1,0,0,1,COUNTA(ChartData!$1:$1))

or to fill for all rows and columns
=OFFSET(ChartData!$A$1,0,0,COUNTA(ChartData!$C:$C),COUNTA(ChartData!$1:$1))

Have a look at the OFFSET function if you want more data on this

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[moontho]

Geoff

This is brilliant. Part of my daily duties at work have been revolutionised

Another star.