Dynamic XY Scatter Chart Question

Robnauticus

New Member
Joined
Dec 16, 2005
Messages
1
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Ceduljko

Board Regular
Joined
Dec 28, 2004
Messages
58
Direction: PivotChart

:)

Correction: PivotChart cannot be used with ScatterXY :(

So, take the nice code bellow.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Would a macro be acceptable?

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,042
Members
412,358
Latest member
Eng Muhammed
Top