0

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.

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:

=OFFSET(Output!$B$1,0,0,nServices) and 
=OFFSET(Output!$C$1,0,0,nServices)

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.

I've tried swapping nServices for a number (15) and still got the error. I've tried the solutions described above

(=OFFSET(Output!$B$1,0,0,max(1,nServices))

and

=iferror(OFFSET(Output!$B$1,0,0,nServices),na()).

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

Any help would be much appreciated as I've spent hours searching for a solution but nothing I've found has worked so far.

Thank you!!

bs123
  • 1,183
  • 3
  • 11
  • 21
S Jacobs
  • 11
  • 2

1 Answers1

0

This worked for me. I created a worksheet-level defined names Data:

=OFFSET(Output!$C$1,0,0,nServices,1)

and Labels:

=OFFSET(Output!$B$1,0,0,nServices,1)

Then the formula for the data series in the graph is:

=SERIES(,Output!Labels,Output!Data,1)

One think you might need to watch out for is using a cut command on cells B1 and C1; cutting those cells when you update data could give a #REF error.

Hope this helps

xidgel
  • 3,085
  • 2
  • 13
  • 22
  • Thank you for answering! Bizarrely when I opened the file this morning after the weekend the error is no longer popping up. I've added the ",1" to the end of the offset forumulae anyway and will see if the error returns. – S Jacobs Aug 10 '15 at 13:47