How do I use a Defined Name range for a value series in a chart?

I would like to create a Defined Name range to use as a the value for a
series in a chart.

Example: Cell B2 equals January Sales; Cell C2 equals February sales; and
cell D2 equals March sales. So, B2:D2 equals sales for Jan, Feb, and Mar. I
can name that range of cells (B2:D2) "Sales".

I would like to have a series in a chart named "Sales". Now, for the Values
of that series, I could put B2:D2. However, I would like to put the Defined
Name "Sales" instead. That way, if I have 5 different charts that graph
monthly sales (B2:D2), to add April sales to the charts, I don't have to go
to each chart and update the Values range to be B2:E2, I could just update my
Defined Name of "Sales" to now be B2:E2, and all of the charts should update
automatically.

In the Series tab of the Source Data dialog, click on the series, and
change =Sheet1!$B$2:$D$2 to =Sheet1!Sales. Alternatively, select the
series, and in the formula bar, change

=SERIES(Sheet1!$A$2,Sheet1!$B$1:$D$1,Sheet1!$B$2:$D$2,1)

to

=SERIES(Sheet1!$A$2,Sheet1!$B$1:$D$1,Sheet1!Sales,1)

Read more about dynamic charts:

http://peltiertech.com/Excel/Charts/Dynamics.html

and about the series formula:

http://peltiertech.com/Excel/ChartsH...esFormula.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Don wrote:

> I would like to create a Defined Name range to use as a the value for a
> series in a chart.
>
> Example: Cell B2 equals January Sales; Cell C2 equals February sales; and
> cell D2 equals March sales. So, B2:D2 equals sales for Jan, Feb, and Mar. I
> can name that range of cells (B2:D2) "Sales".
>
> I would like to have a series in a chart named "Sales". Now, for the Values
> of that series, I could put B2:D2. However, I would like to put the Defined
> Name "Sales" instead. That way, if I have 5 different charts that graph
> monthly sales (B2:D2), to add April sales to the charts, I don't have to go
> to each chart and update the Values range to be B2:E2, I could just update my
> Defined Name of "Sales" to now be B2:E2, and all of the charts should update
> automatically.
>
>