title: How to reference the Nth column in a named range in Excel for plotting?
body: <p>I have a table with, say, 3 rows and 3 columns, the first one being the date and the other two being random data such as below (data start in <code>A1</code>):</p>
  <p><a href="https://i.stack.imgur.com/EjxPi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EjxPi.png" alt="Date Series1 Series2
  Jan  1       10" /></a></p>
  <p>I have created the first named range as <code>'Date'</code> for <code>A2:A4</code> and the second named rage as <code>'Data'</code> for <code>B2:C4</code>.</p>
  <p>I wish to plot <code>Series1</code> using the named range. Of course, I could simply plot <code>Series1</code> manually with a fixed range, but that does not fit what I need to do.</p>
  <p>Most of the documentation I found either refers to single-column named range or does not address the issue of plotting. The closest reply to my question is found <a href="https://stackoverflow.com/questions/2431381/how-do-i-reference-a-cell-within-excel-named-range">here</a> but Excel returns an error when I insert my formula for the plot. Excel returns &quot;This function is incorrect&quot; when I insert <code>=INDEX(Sheet1!Data,,1)</code> in the series values, though this works outside of the charting environment.</p>
  <p>My question is therefore: <strong>can I use a named range with more than one column when charting and, if so, how do I reference the Nth column?</strong></p>
  <p>EDIT: In my broader use case, the range is dynamic and is defined as <code>=OFFSET($B$1,0,0,COUNT($B:$B)-1)</code>. Any solution for plotting should therefore remain purely dynamic.</p>