Chart Title as a Cell in a worksheet
 

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

Chart Title as a Cell in a worksheet
 

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" wrote in message
...
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

Chart Title as a Cell in a worksheet
 

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 ChartOptionsTitle 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 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

Chart Title as a Cell in a worksheet
 

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" 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).

Chart Title as a Cell in a worksheet
 

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

--
Regards
Tom Ogilvy

"John Baker" wrote in message
...
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" 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).

Chart Title as a Cell in a worksheet
 

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" wrote:

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

Chart Title as a Cell in a worksheet
 

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" wrote:

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

Chart Title as a Cell in a worksheet
 

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" wrote in message
...
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" wrote:

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

Chart Title as a Cell in a worksheet
 

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" wrote:


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

Chart Title as a Cell in a worksheet
 

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 Whe=xlLocationAsObject, Name:="Sheet4 (2)"

--
Regards,
Tom Ogilvy

"John Baker" wrote in message
...
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" wrote:

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

Chart Title as a Cell in a worksheet
 

Sub AABBCC()
ThisWorkbook.Names.Add Name:="XRange", _
RefersTo:="=Offset('Data'!$A$2,0,0,Count('data'!$A $2:$A$100),1)"
ThisWorkbook.Names.Add Name:="YRange", _
RefersTo:="=Offset('Data'!$B$2,0,0,Count('data'!$B $2:$B$100),1)"
End Sub


--
Regards,
Tom Ogilvy




"John Baker" wrote in message
...
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" wrote:


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

Chart Title as a Cell in a worksheet
 

Tom:

You have been very tolerant and helpful with a neophyte in this game.

I appreciate your sharing your knowledge

John Baker

"Tom Ogilvy" wrote:

Sub AABBCC()
ThisWorkbook.Names.Add Name:="XRange", _
RefersTo:="=Offset('Data'!$A$2,0,0,Count('data'!$A $2:$A$100),1)"
ThisWorkbook.Names.Add Name:="YRange", _
RefersTo:="=Offset('Data'!$B$2,0,0,Count('data'!$B $2:$B$100),1)"
End Sub