Dynamic range chart losing its dynamism..??

[philcud]

Help,

have set up a named range using offset for the desired chart range,
this is a contiguous range with series in rows and the series names in
the first column (i can do without the xaxis labels for now)

the number of series is high, frm 20 - 150 ish (below the 256 max)

i create a stacked are using a nondynamic range that looks as i want

then right click on chart, source data, type in data range the sheet
name and name range.

Thant works, gives me the data i want

But when i go back to the chart to review the source datam it loses
the formula and just gives me the 'hard-coded' range.

Any ideas on how to give my dynamic chart some dynamism??

 

[philcud]

Help,

have set up a named range using offset for the desired chart range,
this is a contiguous range with series in rows and the series names in
the first column (i can do without the xaxis labels for now)

the number of series is high, frm 20 - 150 ish (below the 256 max)

i create a stacked are using a nondynamic range that looks as i want

then right click on chart, source data, type in data range the sheet
name and name range.

Thant works, gives me the data i want

But when i go back to the chart to review the source datam it loses
the formula and just gives me the 'hard-coded' range.

Any ideas on how to give my dynamic chart some dynamism??

BTW Excel 2003

 

[Jon Peltier]

The data range does not work dynamically like this. It will convert the name
into an address.

You can apply a dynamic range for the X and Y values of each series, tedious
to set up for so many series, but robust and dynamic.

Or you could do some kind of macro that updates the data range. Assuming the
name is "ChartDataRange" and the chart is the only one on the worksheet,
right click on the sheet tab and select View Code. This pops up the code
module for the sheet. In the left dropdown at the top of the module's
window, select Worksheet. In the right dropdown, select Change. Now you have
the following two empty event procedures:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

You can delete the Worksheet_SelectionChange procedure. Copy the middle of
the following and paste it into your Worksheet_Change procedure:

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

Whenever the worksheet changes, it runs this procedure. If the Target (the
changed cell) is included in ChartDataRange, the chart's source data range
is redefined as ChartDataRange.

- Jon

 

[philcud]

Cheers,

looks like i've misunderstood the dynamic part - perhaps a feature
request for a future version of excel??

TY

 

[Jon Peltier]

You're better off learning to do it within the constraints of Excel, than
waiting for a feature request to be fulfilled. If the new feature came out
tomorrow, (a) it would work differently than you would have expected, and
(b) it would be ten years before everyone using your workbook will have
upgraded.

- Jon

 

[Guest]

The simplest way I know to create "dynamic" charts is to create the chart
with a macro. Then all you have to do is have the specfic data page showing
and run the macro, and waza, a chart is born (or perhaps click the top left
cell of the "table". You can record many features in the macro, like
discovering the number of rows, the number of columns (with end down and end
right macro instructions), and use a loop to explicitly define the series
properly. I have found that Microsoft's built in discovery isn't reliable
enough.

For existing charts, I have the chart showing/on top, and then run a macro
and the chart gets formatted or "updated" with its "new" data.