title: Offset formula used in dynamic range for chart generating invalid reference error
body: <p>I have a simple bar chart in Excel 2010 that uses two columns, one for the value labels and one for the data.  Depending on the country the data is for the number of bars ranges from 10-15.  I'm using the offset formula to create a dynamic named range which I then use for the data source for the chart.  However whilst the chart works fine (i.e. when I swap data for another country the number of bars on the chart updates to match the data) I keep getting the "A formula in this worksheet contains one or more invalid references" error popping up.  </p>
  
  <p>Most answers I've found on this topic tackle this issue when it's possible for the named range to contain zero data points, however this is not my problem.  I've tried incorporating Max() and iferror() into my range definition to be sure but neither solution worked.  My formulae are:</p>
  
  <pre><code>=OFFSET(Output!$B$1,0,0,nServices) and 
  =OFFSET(Output!$C$1,0,0,nServices) 
  </code></pre>
  
  <p>The first one feeds the bar labels on the y-axis and the second one feeds the data values.  nServices is a named cell which stores the number of data points and ranges from 10-15.  </p>
  
  <p>I've tried swapping nServices for a number (15) and still got the error. I've tried the solutions described above</p>
  
  <pre><code>(=OFFSET(Output!$B$1,0,0,max(1,nServices))
  </code></pre>
  
  <p>and </p>
  
  <p><code>=iferror(OFFSET(Output!$B$1,0,0,nServices),na()).</code> </p>
  
  <p>I know specifying the number of columns is optional but I've also tried entering 1 and "" after nServices to specify the number of columns.  None of these solutions worked</p>
  
  <p>Any help would be much appreciated as I've spent hours searching for a solution but nothing I've found has worked so far.</p>
  
  <p>Thank you!!</p>