Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

[kelseygueldalewis]

I want to create a dynamic chart that populates values for 3 different categories over a 12 month period based on the current month. I have read up about using the offset function so I have started by naming ranges using the INDIRECT function to use the cell that contains the row indicator for the current month to indicate the first value in the offset argument. Then I want to look 11 rows above (-11), in the same column (0), with a height of 12 (12), and a width of 1 (1). In my head this produces a 1*12 range that includes the data I want to chart for this category.

CumRange
=OFFSET(INDIRECT("B" & Sheet2!$G$3),-11,0,12,1)

L1L2Range
=OFFSET(INDIRECT("C" & Sheet2!$G$3),-11,0,12,1)

TargetRange
=OFFSET(INDIRECT("D" & Sheet2!$G$3),-11,0,12,1)

These data are on Sheet2 and I want to create the graph on Sheet1. However, when I go to create a chart on Sheet1 and input the Chart Range as CumRange, I receive an error that says

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

And this is where I'm stuck. Any help would be much appreciated!