Comment from Jon Peltier
Time: Friday, July 4, 2008, 2:43 pm

Sam – Thanks for the suggestion. Actually your formula links to the bottom right of the range, so this is the appropriate form of the Refers To formula:

=$A$1:INDEX($1:$65536,1+COUNTA($A:$A),1+COUNTA($1:$1))

or, since Excel inserts the sheet name:

=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,1+COUNTA(Sheet1!$A:$A),1+COUNTA(Sheet1!$1:$1))

It’s a good point about volatility of functions. Since I usually have small worksheets, I usually don’t worry about the calculation impact of my range definitions.

Comment from Jon Peltier
Time: Saturday, September 27, 2008, 8:53 pm

Doug -

You need to define a range for each chart, and you should name each chart. To name a chart, hold Shift while selecting the cart, and type a distinctive name in the Name Box. Something like DynoChart1 and DynoChart2. If you have questions about this, see my tutorial Chart Names. Use ranges named DynoRange1 and DynoRange2. Then in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("DynoRange1")) Is Nothing Then
    Me.ChartObjects("DynoChart1").Chart.SetSourceData _
        Source:=Me.Range("DynoRange1"), _
        PlotBy:=xlColumns
  ElseIf Not Intersect(Target, Me.Range("DynoRange2")) Is Nothing Then
    Me.ChartObjects("DynoChart2").Chart.SetSourceData _
        Source:=Me.Range("DynoRange2"), _
        PlotBy:=xlColumns
  End If
End Sub

Comment from Jon Peltier
Time: Friday, October 3, 2008, 4:56 pm

Nick -

In my example, the series names are in the first row of the range “ChtSourceData”. Keep the top left cell blank, put the series names in the rest of the top row of this range, and the X values or category labels in the rest of the first row.

Comment from Jon Peltier
Time: Wednesday, November 19, 2008, 5:58 am

CIBER -

I generally use Forms menu controls for this, but you can use validation just as well.

When using worksheet formulas, usually OFFSET and INDEX are used to define the position and size of a range. In VBA one uses .Offset and .Resize for the same purpose. Your arguments for these functions have to include references to the cells with the validation or control link values.

For example, referring to the Name defined at the top of this article:

Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)

If cell D1 contains the value that indicates my row offset for the start of the data, I would insert it into this formula:

Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,Sheet1!$D$1,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)

Comment from Jon Peltier
Time: Friday, November 6, 2009, 1:38 pm

Ardelan –

How is the source data changing? Is the range changing size?

You might be able to use the utility in How to Edit Series Formulas to adjust the last row, especially if all data ends at the same row.

Comment from Jon Peltier
Time: Saturday, November 7, 2009, 11:36 am

Ardalan -

I see. You may have one to 30 series, and you want the existing data sets to automatically be charted. Any data sets which are removed, you want the series also removed. This could ba accomplished through a dedicated VBA procedure.

I assumed one worksheet, ChartData, which had potentially plottable data in A1:P20, and which also had one embedded chart, the one to be upddated. This procedure clears all series from the chart, checks each pair of columns in the data range, and if both columns contain plottable data (e.g., not errors), a series is added using these two columns.

Sub DynamicChartRange()
  Const sADDRESS As String = "A1:P20"
  Dim rTotal As Range
  Dim cDynamic As Chart
  Dim iSrs As Long

  Set rTotal = Worksheets("ChartData").Range(sADDRESS)
  Set cDynamic = Worksheets("ChartData").ChartObjects(1).Chart

  ' clear the chart
  Do While cDynamic.SeriesCollection.Count > 0
    cDynamic.Legend.LegendEntries(1).LegendKey.Delete
  Loop

  ' add series from total range
  For iSrs = 1 To rTotal.Columns.Count / 2
    ' check columns for data
    If WorksheetFunction.Count(rTotal.Columns(iSrs * 2 - 1)) > 0 And _
        WorksheetFunction.Count(rTotal.Columns(iSrs * 2)) > 0 Then
      ' we have data to plot
      With cDynamic.SeriesCollection.NewSeries
        .Values = rTotal.Columns(iSrs * 2)
        .XValues = rTotal.Columns(iSrs * 2 - 1)
      End With
    End If
  Next

End Sub
 

Comment from Jon Peltier
Time: Thursday, January 14, 2010, 10:58 am

“Me” refers to the sheet that the code is attached to. Since the code fires when the data changes, it should go on the code module for the sheet with the chart data.

Me.Range(“ChtSourceData”) is still correct. To access the chart you need
ThisWorkbook.Sheets(“SheetName”).ChartObjects(1).Chart

Note that the Worksheet_Change event will not detect a change if the formulas update to new values but the formulas themselves are unchanged. You can use the Worksheet_Calculate event, but you have to be careful that this doesn’t really bog things down.

Link formulas shouldn’t bog down the workbook very much.