Excel 2007: Working on a dynamic chart with named ranges. Trying to use the
following formula in my chart (in the 'Chart Data Range:' box):

=SERIES('Tot Revenue'!$M$4,TotRevenueYTDlabels,TotRevenueYTDvalues,1)

...but I am getting an 'Reference is not valid' error. I have checked and
double-checked the names of my ranges and all looks correct. The named ranges
themselves even seem to be correct because I can use the following formula in
my chart and the chart plots correctly with no errors:

TotRevenueYTDlabels:TotRevenueYTDvalues

...the problem with this method is that Excel immediately converts the names
to the actual range values themselves, so when I add data to the ranges, the
chart does not automatically incorporate that data. In other words, when I go
back into the chart to look at the data range, it then reads:

='Tot Revenue'!$L$5:$M$8

Anyone have any ideas? I'm hoping this is not a 2007 problem.