Andy Pope wrote:
You need to include a workbook or worksheet reference when using a named
range.

=SERIES(,Sheet1!$B$4:$B$7,Book1.xls!Net_CLI,1)

Cheers
Andy

It works GREAT. TONS of thanks to you.

Does it mean that anytime I use a Named range, I would have to affix
the worksheet/workbook name as well in charts. Its strange because in
my case, when I was calculating the exact address of the named range, I
had included worksheet names in that. More so because I was using the
named range in the same workbook only

regards,
HP
India



Hari wrote
Hari wrote:

Hari wrote:

Andy,

Thanks for the post. Indirect not being directly usable in Chart series
is new to me.

Im not sure, but somehow, am still not able to make my formula work.

I have :-

Net_CLI =INDIRECT("Trends!$C$4:$" &
VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) &
"$4")

and I created a new named range

NetCLI = Choose (1, Net_CLI)

Both of the above named ranges work correctly when I use them in data
validaton (or as an argument within Sum function).

But If I use NetCLI within Values textbox of my chart series (Source
data dialog box) then I get the error which I mentioned before.


When I try to edit in the formula bar directly by changing
=SERIES(Trends!$B$4,Trends!$C$3:$H$3,Trends!$C$4:$ H$4,1) to
=SERIES(Trends!$B$4,Trends!$C$3:$H$3,NetCLI,1) I get a different error
which is,

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.

Regards,
HP
India


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info