Bubble Chart

[LostinExcel80]

I would like to make a Bubble chart with the following but is is pairing rows together, is there a way to add this quickly for 100's of entries?

.=SERIES('Sheet2 (2)'!$A$3,'Sheet2 (2)'!$B$2:$C$2,'Sheet2 (2)'!$B$3:$C$3,1,'Sheet2 (2)'!$B$4:$C$4)

There are hundreds of them so manual fixing it is out of the question. What I need it to be is:

=SERIES('Sheet2 (2)'!$A$1,'Sheet2 (2)'!$B$2,'Sheet2 (2)'!$C$26,1,'Sheet2 (2)'!$D$2)

Name of plot	Y	X	Z (bubble size)
Name1	1	500	50
Name2	1	700	20
Name3	5	455	30
ect	8	454	46

Thank you

 

[GraH]

Won't it work like this?

 

[Basok]

You idea looks interesting, I will give it a try

 

[LostinExcel80]

There are too many for labels is the only problem with that. Want to be able to highlight one and see the "name of series"

 

[GraH]

Add a second "highlighted" dataseries, and fetch X/Y/Z via index match lookup. Data validation dropdown turns it interactive.

Book1
	B	C	D	E	F
1			X	Y	Z (bubble size)
2	Highlight	Name2	700	1	20
3
4	Name of plot	Y	X	Z (bubble size)
5	Name1	1	500	50
6	Name2	1	700	20
7	Name3	5	455	30
8	ect	8	454	46
9
10
11
Sheet2

Cell Formulas
Range	Formula
D2:F2	D2	=INDEX($C$5:$E$8,MATCH($C2,$B$5:$B$8,0),MATCH(D1,$C$4:$E$4,0))

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$B$5:$B$8

 

[LostinExcel80]

That then only plots 1 and I have 100's of series that need to be plotted

 

[GraH]

Sorry, but I'm getting lost in translation now.

I've shown a way to plot all your data with 1 dataseries in #2.
In #4 you reply "There are too many for labels is the only problem with that. Want to be able to highlight one and see the "name of series"
This highlighting I've show in #5 using a selectable dataseries (C2 is a drop down which highlights and labels that data point only on the chart)

So you have all data points plotted and one highlighted in a dynamic way. What am I missing, not understanding?

 

[Jon Peltier]

What am I missing, not understanding?

He wants whatever bubble he mouses over to show the label.

 

[Jon Peltier]

This is a job for VBA.

First of all, its customary to put X before Y, but I'll assume for whatever reason your data comes in that way and you're stuck with it. Second, et cetera is abbreviated etc, not ect. Sorry, pet peeve.

This VBA procedure will create a bubble chart based on a selected range with 4 columns in the order name, Y, X, and Z. Select the range and run the code.

Sub InsertChartOneBubblePerRow()
  If TypeName(Selection) <> "Range" Then
    MsgBox "Select your data and try again"
    GoTo ExitSub
  End If
  
  Dim rng As Range
  Set rng = Selection
  If rng.Columns.Count <> 4 Then
    MsgBox "Select a four-column range and try again"
    GoTo ExitSub
  End If
  
  Dim cht As Chart
  Set cht = ActiveSheet.Shapes.AddChart2(269, xlBubble).Chart
  Do While cht.SeriesCollection.Count > 0
    cht.SeriesCollection(1).Delete
  Loop
  
  Dim rRow As Range
  For Each rRow In rng.Rows
    If IsNumeric(rRow.Cells(1, 2)) And IsNumeric(rRow.Cells(1, 3)) And IsNumeric(rRow.Cells(1, 4)) Then
      With cht.SeriesCollection.NewSeries
        .Name = "=" & rRow.Cells(1, 1).Address(, , , True)
        .XValues = rRow.Cells(1, 3)
        .Values = rRow.Cells(1, 2)
        .BubbleSizes = rRow.Cells(1, 4)
      End With
    End If
  Next
  
ExitSub:
End Sub

 

[GraH]

He wants whatever bubble he mouses over to show the label.

Oh, okay... That's way out of my league. Leaving that to people like you, who actually know what they're talking about. I just learned the expression "pet peeve". Thanks for that too .