Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
--
I Teach

Excel 2007
Add text bubble labels.
http://www.mediafire.com/file/clnnk0mmzzj/09_22_09.xlsm

Thanks for the information, but what I was looking for was how to create
those labels as a legend. Thanks again.
--
I Teach


"Herbert Seidenberg" wrote:

Excel 2007
Add text bubble labels.
http://www.mediafire.com/file/clnnk0mmzzj/09_22_09.xlsm
.

Hi,

The legend contains the Names of each series. So you would need to create a
series for each Company.
There is a limit of 255 series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
--
I Teach

Thanks, Andy. Please tell me how to do that in Excel 2007.
--
I Teach


"Andy Pope" wrote:

Hi,

The legend contains the Names of each series. So you would need to create a
series for each Company.
There is a limit of 255 series.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
--
I Teach

.

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to each
bubble. Could you please help me to change the macro so that a legend is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.

Based on Herbert's data try this, which will create a new chart and populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.

This worked perfectly - I cannot thank you enough. My only question is that
it only works when the data is formatted as a table. When I convert it to a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not, that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.

If the data is not in a table then you can use something like

Set rngData = ActiveSheet.Range("A1").currentregion


Of course you would need to amend the row/column indexes so you got a
reference to the correct data.

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
This worked perfectly - I cannot thank you enough. My only question is
that
it only works when the data is formatted as a table. When I convert it to
a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not,
that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and
populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend
is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.

Andy, thanks for all your assistance. Now I have two ways to display the
chart, whether the data is in a table or not.

I appreciate all your help and everything worked well.
--
I Teach


"Andy Pope" wrote:

If the data is not in a table then you can use something like

Set rngData = ActiveSheet.Range("A1").currentregion


Of course you would need to amend the row/column indexes so you got a
reference to the correct data.

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
This worked perfectly - I cannot thank you enough. My only question is
that
it only works when the data is formatted as a table. When I convert it to
a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not,
that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and
populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend
is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.


.