Hi:

I have a problem with Excel Charts (Excel 2000 Window 98)

I am trying to set up one basic chart (a simple line chart) that will accept different
data sets of the same type of data. Specifically two columns of dates and values, however
the number of items in a column will vary, so the number of points in the data will vary.
I plan to deal with this by giving the two columns a name and redefining the cells
included in the name each time we open the chart.

A bigger problem (strange as it may seem ) is the chart label. I want to have the chart
label in a cell, and the chart references the cell for its label. I have a Manual (Excel5
Super Book) and it refers to ways that this can be done but they don't work in Excell
2000.

Can someone give me some pointers on this please.

Thanks in advance

John Baker

Create a hard coded title.

then select it

go to the formula bar, put in

=Sheet1!$B$3


for example.

In code:
With ActiveChart.ChartTitle
.HasTitle = True
.Text = "=Sheet1!R3C2"
End With

The reference must be in R1C1 (at least in xl97 and xl2000).



--
Regards,
Tom Ogilvy

"John Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi:
>
> I have a problem with Excel Charts (Excel 2000 Window 98)
>
> I am trying to set up one basic chart (a simple line chart) that will
accept different
> data sets of the same type of data. Specifically two columns of dates and
values, however
> the number of items in a column will vary, so the number of points in the
data will vary.
> I plan to deal with this by giving the two columns a name and redefining
the cells
> included in the name each time we open the chart.
>
> A bigger problem (strange as it may seem ) is the chart label. I want to
have the chart
> label in a cell, and the chart references the cell for its label. I have a
Manual (Excel5
> Super Book) and it refers to ways that this can be done but they don't
work in Excell
> 2000.
>
> Can someone give me some pointers on this please.
>
> Thanks in advance
>
> John Baker

John

If you already have a Title, click on it to select the box. In the formula
bar type an equal sign(=) then go select the cell you want as a title. Hit
ENTER key.

If you don't have a Title, go to Chart>Options>Title and enter any text to get
a Title. Follow foregoing steps.

For dynamic ranges in charts see Tushar Mehta's instructions at

http://www.tushar-mehta.com/excel/ne...rts/index.html

Gord Dibben Excel MVP

On Sat, 02 Oct 2004 21:51:06 GMT, John Baker <(E-Mail Removed)> wrote:

>Hi:
>
>I have a problem with Excel Charts (Excel 2000 Window 98)
>
>I am trying to set up one basic chart (a simple line chart) that will accept different
>data sets of the same type of data. Specifically two columns of dates and values, however
>the number of items in a column will vary, so the number of points in the data will vary.
>I plan to deal with this by giving the two columns a name and redefining the cells
>included in the name each time we open the chart.
>
>A bigger problem (strange as it may seem ) is the chart label. I want to have the chart
>label in a cell, and the chart references the cell for its label. I have a Manual (Excel5
>Super Book) and it refers to ways that this can be done but they don't work in Excell
>2000.
>
>Can someone give me some pointers on this please.
>
>Thanks in advance
>
>John Baker

Thanks. That works fine.

I have found that I now have another similar problem. The X and Y axis parameters don't
appear to accept names of named ranges. Is there some other way I can deal with variations
in the number of rows that will be used in the graph?

Best

John Baker

"Tom Ogilvy" <(E-Mail Removed)> wrote:

>Create a hard coded title.
>
>then select it
>
>go to the formula bar, put in
>
>=Sheet1!$B$3
>
>
>for example.
>
>In code:
> With ActiveChart.ChartTitle
> .HasTitle = True
> .Text = "=Sheet1!R3C2"
> End With
>
>The reference must be in R1C1 (at least in xl97 and xl2000).

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

--
Regards
Tom Ogilvy

"John Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks. That works fine.
>
> I have found that I now have another similar problem. The X and Y axis
parameters don't
> appear to accept names of named ranges. Is there some other way I can deal
with variations
> in the number of rows that will be used in the graph?
>
> Best
>
> John Baker
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote:
>
> >Create a hard coded title.
> >
> >then select it
> >
> >go to the formula bar, put in
> >
> >=Sheet1!$B$3
> >
> >
> >for example.
> >
> >In code:
> > With ActiveChart.ChartTitle
> > .HasTitle = True
> > .Text = "=Sheet1!R3C2"
> > End With
> >
> >The reference must be in R1C1 (at least in xl97 and xl2000).
>

Tom:

I gather your not using the "Wizard" or any of the other tools, but just straight VB. Is
tha right?

I have never used VB for charts, so this is something new for me.

John

"Tom Ogilvy" <(E-Mail Removed)> wrote:

>in the dialog for assigning ranges
>
>=Sheetname!RangeName
>
>or
>
>=BookName.xls!RangeName
>
>works for me.

Tom:

To make things simple, I set up a chart and then recorded a macro to change the data
ranges (which I did with cells initially), I then subsituted a named range for the cells
as in:

Sub setparms()
'
' setparms Macro
' Macro recorded 10/02/2004 by John H Baker
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=data!plotdata, PlotBy:= _
xlColumns
ActiveWindow.Visible = False
Windows("ReportNameChart.xls").Activate

End Sub

Chart1 is the chart. However, it does not care for the "data!plotdata" reference, which is
sheet and named range. I also tried "plotdata" and that was not accepted either. Is this a
flaw in my syntax or am I barking up the wrong tree?

John Bakler


"Tom Ogilvy" <(E-Mail Removed)> wrote:

>in the dialog for assigning ranges
>
>=Sheetname!RangeName
>
>or
>
>=BookName.xls!RangeName
>
>works for me.

for the last question, I was using manual methods. I only included the VBA
in the first part because it isn't clear what you are doing and you did post
in programming.

Define you names (Xrange and Yrange as examples - see below)

Create your chart with the wizard and when you get to the data source part,
go to the Series tab and put in your entries for your defined names

='Sheet4 (2)'!Xrange

='Sheet4 (2)'!Yrange

as an example.

My defined names
Xrange
=Offset('Sheet4 (2)'!$G$23,0,0,Count('Sheet4 (2)'!$G$23:$G$40),1)

YRange
=Offset('Sheet4 (2)'!$H$23,0,0,Count('Sheet4 (2)'!$H$23:$H$40),1)

--
Regards,
Tom Ogilvy


"John Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tom:
>
> I gather your not using the "Wizard" or any of the other tools, but just
straight VB. Is
> tha right?
>
> I have never used VB for charts, so this is something new for me.
>
> John
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote:
>
> >in the dialog for assigning ranges
> >
> >=Sheetname!RangeName
> >
> >or
> >
> >=BookName.xls!RangeName
> >
> >works for me.
>

Tom:

I tried this and it almost works, but there is some proboemn with the reference. "data" is
the sheet, and A or B 2 are the cells which start the columns to be used.

Thanks a lot for all your help

Sub defineranges()
'
' defineranges Macro
' Macro recorded 10/02/2004 by John H Baker

Xrange=Offset('Data'!$a$2,0,0,Count('data'!$a$2:$b$100),1)

YRange=Offset('Data'!$b$2,0,0,Count('Data'!$b$2:$b$100),1)

'
End Sub

PS it dosent make any difference if data is in quotes or not- neother are acceptable.


John

"Tom Ogilvy" <(E-Mail Removed)> wrote:

>
>YRange
>=Offset('Sheet4 (2)'!$H$23,0,0,Count('Sheet4 (2)'!$H$23:$H$40),1)

You need to do the X and Y values separately:

ActiveChart.SeriesCollection(1).XValues = "=Book2!XRange"
ActiveChart.SeriesCollection(1).Values = "=Book2!YRange"


or

ActiveChart.SeriesCollection(1).XValues = "='Sheet4 (2)'!XRange"
ActiveChart.SeriesCollection(1).Values = "='Sheet4 (2)'!YRange"



Macro:

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet4 (2)").Range("G23:H27"),
_
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Book2!XRange"
ActiveChart.SeriesCollection(1).Values = "=Book2!YRange"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet4 (2)"

--
Regards,
Tom Ogilvy

"John Baker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tom:
>
> To make things simple, I set up a chart and then recorded a macro to
change the data
> ranges (which I did with cells initially), I then subsituted a named range
for the cells
> as in:
>
> Sub setparms()
> '
> ' setparms Macro
> ' Macro recorded 10/02/2004 by John H Baker
> '
>
> '
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.ChartArea.Select
> ActiveChart.SetSourceData Source:=data!plotdata, PlotBy:= _
> xlColumns
> ActiveWindow.Visible = False
> Windows("ReportNameChart.xls").Activate
>
> End Sub
>
> Chart1 is the chart. However, it does not care for the "data!plotdata"
reference, which is
> sheet and named range. I also tried "plotdata" and that was not accepted
either. Is this a
> flaw in my syntax or am I barking up the wrong tree?
>
> John Bakler
>
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote:
>
> >in the dialog for assigning ranges
> >
> >=Sheetname!RangeName
> >
> >or
> >
> >=BookName.xls!RangeName
> >
> >works for me.
>