Dynamic range in chart

  • Thread starter Thread starter Hari
  • Start date Start date
H

Hari

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
 
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
 
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
 
Hari said:
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.
Click to expand...

Could somebody please outline as to what am doing wrong.

Regards,
HP
India
 
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
 
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
 
Andy said:
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
Click to expand...

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
 
Back
Top