Thread: Using Dynamic Named Range in Chart

In one tab, "Irrig Data," I have a data set spanning several years, with one entry per month. I have created a named range of the current year's data with dimensions 1 Column by N Rows, where N is the number of entries made so far this year. In other words, this range expands as I add new entries to the spreadsheet. Here is what I used to name the range:

=OFFSET( INDIRECT(ADDRESS(7+12*FLOOR(COUNTA('Irrig Data'!$G$7:$G$66)/12,1),7,1,TRUE,"Irrig Data")),  0,  0,  COUNTA(OFFSET('Irrig Data'!$G$7,12*FLOOR(COUNTA('Irrig Data'!$G$7:$G$66)/12,1),0,12)) )

The first parameter calculates a reference to the first entry of the current year (yes, my data start at the 7th row). The next two parameters are simply filler, since I don't need to adjust the range I am referencing. The last parameter counts how many entries have been made this year, which sets the number of rows to use for the range.

When I try to use this range in a chart, I get the following error:

A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference.

Any ideas on how I can get this range to show up in my graph by avoiding this error?

Thanks!

Here are some links that might be helpful:

http://office.microsoft.com/en-us/ex...001109801.aspx
http://chandoo.org/wp/2009/10/15/dyn...t-data-series/
http://www.gilliganondata.com/index....your-charts-2/

Thanks guys, I got it to work by simplifying my formula. Since I'm trying to minimize my number of tabs, I ended up combining ChemistB's suggestion with the suggestion posted on Microsoft's website, referenced by Whizbang.

You guys rock! Thank you so much, and sorry for the late response.