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