Problems with XY-Scatter

[PO]

Excel 2003

Hi,

I have set up an employee table with around 1000 employees in a worksheet.
Each record consists of the following columns:

Name
Grade (1-5)
Trend (1-3)

I want Grade on the y axis and Trend on the x axis. Each dot in the chart
represents 1 employee. To accomplish this I loop through the table adding
each employee (the code below is just a simple example):

Sub PopulateChart()

Dim I1 As Integer
Dim sc As Object

ActiveSheet.ChartObjects("Diagram 6").Activate

For I1 = 2 To 1000 Step 1
Set sc = ActiveChart.SeriesCollection.NewSeries


sc.XValues = "=Sheet1!R" & I1 & "C3"
sc.Values = "=Sheet1!R" & I1 & "C4"
sc.Name = "=Sheet1!R" & I1 & "C2"
Next I1

End Sub

This works fine but the problem is that there's a limit and I can only add
255 series to the chart.
Normally you don't want to see all 1000 employees plotted in the chart but
it's useful to get an overview and for finding groups of ppl that deviate
somehow from the rest of the population. The next step would for example be
to apply a filter to only view the employees with average grade (3) and a
negative trend.(1).

Is there another way to populate the chart so all employees are fitted? Or
could I maybe use another chart to accomplish the same thing?

Regards
Pete

 

[Jon Peltier]

Okay, make it simple. You only need one series, and labels made by one of
these free and powerful Excel utilities:

Rob Bovey's Chart Labeler, http://appspro.com/Utilities/ChartLabeler.htm
John Walkenbach's Chart Tools,
http://www.j-walk.com/ss/excel/files/charttools.htm

Download and install one, then restart Excel.

Put the trend data to the left of the Grade data. Select these two columns
and make an XY chart. Trend, in the first column, will be X. Now use the
utility you downloaded to add the labels from the first column as data
labels on this series.

- Jon

 

[PO]

Thanks Jon! Exactly what I was looking for.

Regards
Pete