This is a cross posting I am attaching my example in this one. DynamicNamedRangeIssue.xls

Please see the attached excel file.
I am trying to create a chart with YOY growth on secondary axis. I will be having many charts in the workbook hence want to create a chart using dynamic named range which will return values of YOY growth as an array which I can use as the source for the secondary axis chart. The data looks like below and the formula for YOY values is. =(B14-B2)/B2 * 100 ie (current value - last year value)/(last year value) (First 12 values will be obviously #N/A as a year is not elapsed) The data labels start at A1.

I have about 50 values for this example. I have setup following named ranges.
1. xValues = =Sheet1!$A$2:$A$51
2. YearOffset = =ROW(OFFSET(Sheet1!$A$1,0,0,COUNTA(xValues),1))-12
Year offset returns array -11, -10, -9....38 for 50 values
3. yValues = =Sheet1!$B$2:$B$51
4. yValues2 = =IF(YearOffset<=0,NA(),(INDEX(yValues,YearOffset+12)-INDEX(yValues,YearOffset))/INDEX(yValues,YearOffset)) * 100

My problem as you see in attached is that the chart using named ranges does not work however it returns correct values in cells. I am not too sure if excel can use the array returned by my named range as a series.DynamicNamedRangeIssue.xls