Tables and X Y Scatter Charts

[sherriwatkins]

I need some help! I am usually really fluent in all things Excel, but this has me stumped. I have a table with over 1350 rows of data. I am trying to create an XY scatter chart that will allow me to chart only the information that has been sorted in the table. This table has 6 sets of data for the Y and 6 sets of data for the X axis. Y is the power and X is the flow (see below). I know I can manually selecteach row to put in the chart but is there a way to have Excel do this for me since there is so much data? I need to beable to create a master scatter chart that will take my raw data and as Ifilter rows out, will automatically adjust the chart. I hope this makes sense.

Manufacturer	Model Number	Date	Type	Stage	Pressure	HP	Flow	Power
AC	G36	11/20/13	A	1	102	50	240.0	220.0	150.0	100.0	50.0	30.0	21.00	20.00	19.00	19.00	25.00	27.00
AC	G37	1/16/14	A	2	115	50	235.0	220.0	150.0	100.0	45.0	35.0	21.00	21.00	19.00	19.00	24.00	25.00
BC	M23	1/16/14	W	1	125	50	225.0	215.0	150.0	95.0	35.0	30.0	22.00	21.00	20.00	20.00	26.00	26.00
BC	M24	7/16/14	W	2	102	40	205.0	185.0	150.0	95.0	40.0	30.0	19.00	18.00	18.00	17.00	21.00	22.00

<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="40" style="width: 30pt;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="40" style="width: 30pt;" span="13"> <tbody> </tbody>

Thanks,
Sherri

 

[Worf]

Hi Sherri

If you filter a chart’s source table or manually hide some rows, the corresponding data disappears from the chart. Does this solve your problem?
For a more refined approach, we can use VBA.
Note that the maximum number of data series per chart is 255.

 

[sherriwatkins]

@ Worf Thanks, Is there an easy way to add the data to the chart rather than one at a time? Since there are over 1300 rows of data in this one file (I have 4 others), it would take months to enter each row into the chart.

Thanks!
Sherri

 

[Worf]

This can be automated with VBA. The code below adds only the filtered rows from the source table to the chart, what do you think?

[FONT=Courier New][SIZE=3][COLOR=#000000] 
Sub Sherri()
Dim t As ListObject, r As Range, i%, ch As ChartObject, j%
Set ch = ActiveSheet.ChartObjects("chart 1")                ' your chart name here
Set t = ActiveSheet.ListObjects("table3")                   ' your table name here
Set r = t.DataBodyRange.SpecialCells(xlCellTypeVisible)
Do While ch.Chart.SeriesCollection.Count > 0
    ch.Chart.SeriesCollection(ch.Chart.SeriesCollection.Count).Delete
Loop
ch.Chart.HasLegend = True
For i = 1 To r.Areas.Count
    For j = 1 To r.Areas(i).Rows.Count
        With ch.Chart.SeriesCollection.NewSeries
            .XValues = r.Areas(i).Rows(j).Cells(1, 8).Resize(1, 6)  ' columns H-M
            .Values = r.Areas(i).Rows(j).Cells(1, 14).Resize(1, 6)  ' columns N-S
            .Name = r.Areas(i).Rows(j).Cells(1, 2)
        End With
Next j, i
End Sub<strike></strike>
[/COLOR][/SIZE][/FONT]
<strike></strike>