Chart attached to dynamic range

[Avi]

Hello,

I try to create a multiple a chart based on three columns and a variable nb of columns where the columns are the X axis

a1 b1 c1
bbb 4 5 7
ccc 5 6 4
ddd 3 5 3


To do that i create a dynamic ranged name and put it in the Range field in the chart wizard. The problem is after doing that, the ranged name becomes an usual static range address. Is there a way to keep the cahrt linked to the Named range instead?

Thanks a lot for your help


Avi Benita 054-4660641 wwwAvi Benita 054-4660641 www.avibenita.com

 

[Jon Peltier]

You can link the name, xvalues, and yvalues of a chart series to
individual named ranges, but you can't link the entire source data range
to a named range.

You can mimic the effect you want with a VBA procedure. Assuming your
range of interest is named SourceData, and the chart is the first chart
object on the sheet, right click the sheet tab, select View Source, and
paste this procedure into the code module that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("SourceData")) Is Nothing Then
ChartObjects(1).Chart.SetSourceData Source:=Range("SourceData")
End If
End Sub

This is an event procedure, which is triggered by the event of the user
changing the SourceData range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______