Horizontal Dynamice Range using OFFSET

I'm familiar with using OFFSET to make dynamic vertical ranges, but I am trying to make one out of a horizontal range and I keep getting a cell reference error. I have 2 sheets. One where data is recorded (Plant 5S Score Summary Data) and one where a chart is displayed (Dashboard). I want the range of data displayed on the chart to be a function of the current month entered on the Dashboard tab. I have this value assigned to a column number on the Summary data tab, so that in cell AY2 on the summary data tab, the column number for the current month is displayed.

I have set up my dynamic range for the summary score to be like this.
PlantSum =OFFSET(INDIRECT(INDEX('Plant 5S Score Summary Data '!$AY$2,'Plant 5S Score Summary Data '!$AY$3)),0,-11,1,12)

Where AY2 is the reference row number for the current month and
Where AY3 is the reference column number for the current month

However, when I try to plug this named range into the chart for the data,
='Dashboard_Horizontal_Range.xlsx'!PlantSum

I get an error message that reads
We found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names and links to other workbooks in your formulas are all correct.

I have read that sometimes blank/o values can cause issues with named ranges, but the range that I am targeting doesn't have 0 values, or at least I can't find them.

It's not issue with OFFSET, but rather misuse of INDEX function and INDIRECT that's causing issue.

Try changing name range formula to...

Please Login or Register  to view this content.

Thanks CK76 - that solved it!