Chart Title as a Cell in a worksheet

[John Baker]

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

 

[Tom Ogilvy]

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

 

[Gord Dibben]

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/newsgroups/dynamic_charts/index.html

Gord Dibben Excel MVP

 

[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]

in the dialog for assigning ranges

=Sheetname!RangeName

or

=BookName.xls!RangeName

works for me.

 

[John Baker]

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

 

[John Baker]

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]

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)

 

[John Baker]

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]

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

 

[Tom Ogilvy]

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

 

[John Baker]

Tom:

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

I appreciate your sharing your knowledge

John Baker