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

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Thanks Tim. I am trying to replicate these corrections into my unedited sheet I get a circular references error.
In your comment you say just add the sheetname for named Dynamic Range, however in the example you use the workbook name, not the sheetname - does it make a difference?
Any ideas on the circular error?

I cleared and hid the cells from my original document to show you how it looks more realistically on my end. Can you get it to work on teh Dashboard and Raw Data Sheets?

I am using the Sheet name, in this case the sheet name is 'Raw Data' but I still get teh same error. That there is a problem with one or more formula references in the worksheet.

I ran and error check on the dashboard and raw data sheets but there were no errors.

dataTABLE =chartdata!$B$4:INDEX(chartdata!$D:$D,COUNTA(chartdata!$D:$D)+1,1)
Column_B =INDEX(dataTABLE,chartdata!$G$3-11,1):INDEX(dataTABLE,chartdata!$G$3,1)
Column_C =INDEX(dataTABLE,chartdata!$G$3-11,2):INDEX(dataTABLE,chartdata!$G$3,2)
Column_D =INDEX(dataTABLE,chartdata!$G$3-11,3):INDEX(dataTABLE,chartdata!$G$3,3)

I changed the name of that sheet ( )

I need more info. That chart was made with Excel 2007 so I don't know what "function" you are referring to.

If I understand correctly changing the formula for 'Axis Title' so that it reads as follows should do what you want:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.