Issues with Dynamicaly adding Series to a Scatter Plot

[geotek]

Hello,
I'm trying to create a dynamic chart with XL2010 that will auto update as new series are added to the workbook. I've done a pretty exhaustive search online and on MrExcel which has helped, but I'm still not there yet and I'm hoping that someone here can help.

I'm reviewing data that comes in each month. The table is set up with Column A being the reading depth and each of the subsequent columns being the values recorded at depth. The top row of each column is the date the readings were taken. The tables are generally fixed in the number of rows but the number of columns continue to increase each month. An example of the data:

Depth	1/11/2013	2/5/2013	2/26/2013
2	-20.2332	-19.2332	-18.2332
4	-19.3	-18.3	-17.3
6	-18.6	-17.6	-16.6
8	-18.4	-17.4	-16.4
10	-18.1	-17.1	-16.1
12	-17.8	-16.8	-15.8
14	-17.5	-16.5	-15.5
16	-17.3	-16.3	-15.3
18	-17.03	-16.03	-15.03
20	0	0	0

This table is an example only. There are 120 rows in the table I'm dealing with and 30 columns. For this example the next data will be entered into the next open column for 3/5/2013. When I plot the data, I plot the depth data on the Y-axis and the date data on the X-axis. This allows me to see change in a value at a specific depth over time. I have tried creating a named table for the data using the offset command

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)+1,COUNTA(Sheet1!$1:$1)+1)

I then created a chart and inserted the named table into the series.

=SERIES(Sheet1!$B$1,'Inclinometer Plotsheets-2.xlsm'!DataValues,Sheet1!$A$2:$A$50,1)

This seems to be where I'm going wrong.

As an aside, I was able to get some VBA code from http://peltiertech.com/dynamic-chart-source-data/ that almost enters the new data into the chart by resizing the data set, but again, I haven't been able to make it work correctly with the X-Y scatter plot.

Sub UpdateChartSourceData()
  With ActiveSheet
    .ChartObjects(1).Chart.SetSourceData _
        Source:=.Range("DataValues"), _
        PlotBy:=xlColumns
  End With
End Sub

Any help would be greatly appreciated. I've spend the better part of 3 days scouring the internet and forums with limited success.

 

[Jon Peltier]

This would work if you used the conventional arrangement of X in the first column and Y in any subsequent columns. But your data switched X and Y, so you need to do more work.

Change UpdateChartSourceData to this, and run it manually or call it when the DataValues range is updated.

Sub UpdateChartSourceData()
  Dim rData As Range
  Dim rX As Range, rY As Range, rSrsName As Range
  Dim iSrs As Long
  Dim cht As Chart
  
  Set rData = ActiveSheet.Range("DataValues")
  Set cht = ActiveSheet.ChartObjects(1).Chart
  
  Do While cht.SeriesCollection.Count > 0
    cht.SeriesCollection(1).Delete
  Loop
  
  For iSrs = 1 To rData.Columns.Count - 1
    Set rY = rData.Columns(1).Offset(1).Resize(rData.Rows.Count - 1)
    Set rX = rY.Offset(, iSrs)
    Set rSrsName = rX.Offset(-1).Resize(1)
    
    With cht.SeriesCollection.NewSeries
      .Values = rY
      .XValues = rX
      .Name = "=" & rSrsName.Address(, , , True)
    End With
  Next
  
End Sub

 

[geotek]

Jon,
thank you very much. That code works like a charm!