Dynamic range in chart

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

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

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

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.
>

Could somebody please outline as to what am doing wrong.

Regards,
HP
India

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

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

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

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