*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***


The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882

Excel does not let you use named ranges for source data for the whole
chart, only for the components (name, x values, y values) for each series.

You may be able to use this approach to get around this limitation:

Dynamic Chart Source Data (VBA)
http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/13/2010 11:46 AM, MikeF wrote:
*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***


The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882

Jon,

Thanx for the assistance.
As follows is the adjusted sub:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

.... It goes to the correct page, but Debug yellows the following:
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns

Am I missing proper syntax re chart name or something else on the first line?

- Mike


"Jon Peltier" wrote:

Excel does not let you use named ranges for source data for the whole
chart, only for the components (name, x values, y values) for each series.

You may be able to use this approach to get around this limitation:

Dynamic Chart Source Data (VBA)
http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/13/2010 11:46 AM, MikeF wrote:
*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***


The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882


.

This was written for a chart embedded on the active worksheet.

For a chart on a standalone sheet, this should do it:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveChart
.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/14/2010 5:43 PM, MikeF wrote:
Jon,

Thanx for the assistance.
As follows is the adjusted sub:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

... It goes to the correct page, but Debug yellows the following:
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns

Am I missing proper syntax re chart name or something else on the first line?

- Mike


"Jon Peltier" wrote:

Excel does not let you use named ranges for source data for the whole
chart, only for the components (name, x values, y values) for each series.

You may be able to use this approach to get around this limitation:

Dynamic Chart Source Data (VBA)
http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/13/2010 11:46 AM, MikeF wrote:
*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***


The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882


.

Jon,

The sub stalls, and "blues out" specifically the following:
..Range

Error is....
Compile Error
"Method or Data Member Not Found"

Definitely both the sheet and range names are absolutely correct.

Is there anything I'm missing, syntax, or ..??

Thanx.
- Mike

"Jon Peltier" wrote:

This was written for a chart embedded on the active worksheet.

For a chart on a standalone sheet, this should do it:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveChart
.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/14/2010 5:43 PM, MikeF wrote:
Jon,

Thanx for the assistance.
As follows is the adjusted sub:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

... It goes to the correct page, but Debug yellows the following:
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns

Am I missing proper syntax re chart name or something else on the first line?

- Mike


"Jon Peltier" wrote:

Excel does not let you use named ranges for source data for the whole
chart, only for the components (name, x values, y values) for each series.

You may be able to use this approach to get around this limitation:

Dynamic Chart Source Data (VBA)
http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/13/2010 11:46 AM, MikeF wrote:
*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***


The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882


.

.

Since that line is in the with/end with structu

With ActiveChart
.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With

The .range(...) refers to the activechart.

So either try:

With ActiveChart
.SetSourceData _
Source:=.parent.parent.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With

or

With ActiveChart
.SetSourceData _
Source:=activesheet.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With

The .parent of the chart is the chartobject (Jon will correct me (soon!)). The
..parent of the .parent (or the parent of the chartobject) is the worksheet that
owns the chart.



MikeF wrote:

Jon,

The sub stalls, and "blues out" specifically the following:
.Range

Error is....
Compile Error
"Method or Data Member Not Found"

Definitely both the sheet and range names are absolutely correct.

Is there anything I'm missing, syntax, or ..??

Thanx.
- Mike

"Jon Peltier" wrote:

This was written for a chart embedded on the active worksheet.

For a chart on a standalone sheet, this should do it:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveChart
.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/14/2010 5:43 PM, MikeF wrote:
Jon,

Thanx for the assistance.
As follows is the adjusted sub:

Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub

... It goes to the correct page, but Debug yellows the following:
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns

Am I missing proper syntax re chart name or something else on the first line?

- Mike


"Jon Peltier" wrote:

Excel does not let you use named ranges for source data for the whole
chart, only for the components (name, x values, y values) for each series.

You may be able to use this approach to get around this limitation:

Dynamic Chart Source Data (VBA)
http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/13/2010 11:46 AM, MikeF wrote:
*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***


The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.

The €œ4/1/2010€� is in cell c1.

There is a dynamic range in use, where c1.cc6 is named €œdynRngSalesProg€� €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)

€¦ But the chart [series] turns that into a static range.

On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6

Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).

I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.

Is there anything that can be done with this situation?

REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882


.

.


--

Dave Peterson