Refering to a named range in a chart: 2007
 

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,TotRevenueYTDval ues,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.

Refering to a named range in a chart: 2007
 

Ok, solved my own problem. For anyone else with this issue...here's the
correct formula for the Chart Data Range:

=SERIES(,'WFG Finances temp.xlsx'!TotRevenueYTDlabels,'WFG Finances
temp.xlsx'!TotRevenueYTDvalues,1)

Looks like what I was missing was putting the name of the entire workbook in
front of the named ranges.

- fruitticher

"fruitticher" wrote:

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,TotRevenueYTDval ues,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.