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

[Guest]

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, B22 equals sales for Jan, Feb, and Mar. I
can name that range of cells (B22) "Sales".

I would like to have a series in a chart named "Sales". Now, for the Values
of that series, I could put B22. However, I would like to put the Defined
Name "Sales" instead. That way, if I have 5 different charts that graph
monthly sales (B22), 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.

 

[Jon Peltier]

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/ChartsHowTo/ChartSeriesFormula.html

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