Bubble Chart Legend

[I Teach]

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

[Herbert Seidenberg]

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

[I Teach]

Is it possible to create a legend for a bubble chart in Excel 2007 using a
fourth range of data?
--
I Teach

[I Teach]

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:

> .
>

[Andy Pope]

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" <Ite...@discussions.microsoft.com> wrote in message
news:B59B6A17-F39F-424D...@microsoft.com...

[I Teach]

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

"Andy Pope" wrote:

> .
>

[Andy Pope]

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

[I Teach]

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:

> .
>

[Andy Pope]

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" <Ite...@discussions.microsoft.com> wrote in message

news:093B50C7-C3DF-4691...@microsoft.com...

[I Teach]

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:

> .
>

[Andy Pope]

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" <Ite...@discussions.microsoft.com> wrote in message

news:D2463534-82F2-42DA...@microsoft.com...

[I Teach]

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:

> .
>