Dynamic XY Scatter Chart Question

[Robnauticus]

Hello Everyone,

I am designing a chart based on 3 simple columns pulled from an access db. The fist colum I would like as my series label. The second is the items quantity and the third is the cost.

Example

DivisionID DrywallMBF Cost
LSC 11095 6500
LSC 12095 7300
LSC 10095 7000
USH 14995 5900
USH 14995 5675


The problem is I need it to display every point that is entered (could be up to 60-100). I need the series to be grouped so all of the LSC entries have one symbol and USH have another.

I am have all the points in the proper place and the axes are correct.

I also do not want to label anything manually since this is all supposed to be automated.

Any direction would be appreciated!!!

Rob

 

[Ceduljko]

Direction: PivotChart



Correction: PivotChart cannot be used with ScatterXY

So, take the nice code bellow.

 

[Andrew Poulsom]

Would a macro be acceptable?

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cht As Chart
    Dim r As Long
    Dim i As Long
    Dim Ser As Series
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    Set Cht = Charts.Add
    With Cht
        .ChartType = xlXYScatter
        .Location Where:=xlLocationAsObject, Name:=Sh.Name
    End With
    Set Cht = ActiveChart
    With Cht
        r = 2
        For i = 2 To Rng.Rows.Count
            If Rng.Cells(i, 1) <> Rng.Cells(i + 1, 1) Then
                Set Ser = .SeriesCollection.NewSeries
                With Ser
                    .XValues = "='" & Sh.Name & "'!R" & r & "C2:R" & i & "C2"
                    .Values = "='" & Sh.Name & "'!R" & r & "C3:R" & i & "C3"
                    .Name = "='" & Sh.Name & "'!R" & r & "C1"
                End With
                r = i + 1
            End If
        Next i
    End With
End Sub

The code assumes that the data is sorted by DivisionID. Change the worksheet reference to suit.