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.

Please guide me.

regards,
HP
India


Andy Pope wrote:
Hi,

The problem is that chart series can not directly use the result of an
INDIRECT() formula.

One way around this is to reference the named range via another named range.

Named range CHTA builds an indirect reference to the charts labels. The
labels start in A2 on sheet1 and the number of labels in controlled by
the end cell reference which is stored in E3. So for our example

Cell E3: =A10
Named range CHTA: =INDIRECT("Sheet1!A2:"&Sheet1!$E$3)

Add a named range that the chart can use plus one of the data in the
next column.

CHTLABELS: =Choose(1,CHTA)
CHTDATA: =OFFSET(CHTLABELS,0,1)

Now the chart series formula will look like this.

=SERIES(,Book1!CHTLABELS,Book1!CHTDATA,1)

Cheers
Andy

Hari wrote:
Hi,

I have created a named range called Net_CLI which has the following
formula =INDIRECT("Trends!$C$4:$" &
VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) &
"$4")

Presently I expect the vlookup part of the above formula to return H,
so in all I expect the named range to give me ---- Trends!$C$4:$H$4

If I use the above named range in Data Validation, then I get the
relevant values in drop down.

But If I have a chart (in the same worksheet) in which for the first
series in place of existing entry in the values textbox as
=Trends!$C$4:$H$4 , I enter as Net_CLI , then I get the following
message:-

"The formula you typed contains an error. Try one of the following:

- Make sure you've included all parantheses and required arguments.
- To get assistance with a function....
- ...
- ...
-..."

What is the mistake am doing above.

regards,
HP
India


--

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