title: Excel VBA labelling a second data series
body: <p>I have created a multiple chart trend template that dynamically updates using the offset function depending on user input.</p>
  
  <p>I have been able to label the first data series using a defined name that uses the offset function, but I have been unable to label the second data series in the same way ...</p>
  
  <p>So INDEX(name,1) works to pull the relevant part of the array within a cell ... </p>
  
  <p>Is there a way to dynamically create a number of textboxes and link to named cells?  Possibly something like this? <a href="https://stackoverflow.com/questions/17582636/attaching-a-textbox-to-a-point-or-line-on-a-chart-in-excel-vba">Attaching a Textbox to a point or line on a chart in Excel/VBA</a> </p>
  
  <p>The following code works but only with the set of defined names in one worksheert. Is there a way to modify this for multiple charts, using named cells on different worksheets?</p>
  
  <pre><code>Sub Add_Text_to_point()
  
  Dim objCht As ChartObject
  Dim ws As Worksheet
  
  ' loop through sheets in this workbook
  For Each ws In ThisWorkbook.Worksheets
  ' loop through Chartobjects in sheet
  For Each objCht In ws.ChartObjects
      ' no need to select the chart or the series use With statement instead
      With objCht.Chart.SeriesCollection(5).Points(1)
              .DataLabel.Text = [Point1]
      End With
      With objCht.Chart.SeriesCollection(5).Points(2)
              .DataLabel.Text = [Point2]
      End With
      With objCht.Chart.SeriesCollection(5).Points(3)
              .DataLabel.Text = [Point3]
      End With
      With objCht.Chart.SeriesCollection(5).Points(4)
              .DataLabel.Text = [Point4]
      End With
      With objCht.Chart.SeriesCollection(5).Points(5)
              .DataLabel.Text = [Point5]
      End With
      With objCht.Chart.SeriesCollection(5).Points(6)
              .DataLabel.Text = [Point6]
      End With
      With objCht.Chart.SeriesCollection(5).Points(7)
              .DataLabel.Text = [Point7]
      End With
      With objCht.Chart.SeriesCollection(5).Points(8)
              .DataLabel.Text = [Point8]
      End With
      With objCht.Chart.SeriesCollection(5).Points(9)
              .DataLabel.Text = [Point9]
      End With
      With objCht.Chart.SeriesCollection(5).Points(10)
              .DataLabel.Text = [Point10]
      End With
      Next objCht
   Next ws
  </code></pre>