Formulas in chart series references
 

Does anyone know if it possible to have formulas embedded in the SERIES
formula that defines a chart series. For example, what I'd like to have is

=SERIES(Sheet1!$C$18,Sheet1!$D$16:$F$16,INDIRECT(D 12),1)

where D12 is a valid text range.

This does not seem to work. Essentially, I am trying to set up the chart so
I can toggle what is charted without needing to alter the chart references OR
set up a separate set of cells that are charted and that in turn refer to the
(changing) data set I want to chart.

Any thoughts are welcome!

You can't embed any formulas into the SERIES formula. What you can do is
define a named range (sometimes called a named formula) in the
worksheet, then refer to this named range in the series formula.

For instance, you could set up a named range for your example like this:

Go to Define Names dialog: Insert menu, Names item, Define (or use the
Ctrl+F3 shortcut). Enter a name, like RangeY, and a formula in Refers
To, like =INDIRECT(D12) or =INDIRECT($D$12).

Now edit your series formula:

=SERIES(Sheet1!$C$18,Sheet1!$D$16:$F$16,Sheet1!Ran geY,1)

There is a wealth of information on the internet about these dynamic
charts. I have some examples and some links on my web site:

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

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


Guackyxxx wrote:

Does anyone know if it possible to have formulas embedded in the SERIES
formula that defines a chart series. For example, what I'd like to have is

=SERIES(Sheet1!$C$18,Sheet1!$D$16:$F$16,INDIRECT(D 12),1)

where D12 is a valid text range.

This does not seem to work. Essentially, I am trying to set up the chart so
I can toggle what is charted without needing to alter the chart references OR
set up a separate set of cells that are charted and that in turn refer to the
(changing) data set I want to chart.

Any thoughts are welcome!

Thanks, Jon, I knew you'd come through for me (as always!)

"Jon Peltier" wrote:

You can't embed any formulas into the SERIES formula. What you can do is
define a named range (sometimes called a named formula) in the
worksheet, then refer to this named range in the series formula.

For instance, you could set up a named range for your example like this:

Go to Define Names dialog: Insert menu, Names item, Define (or use the
Ctrl+F3 shortcut). Enter a name, like RangeY, and a formula in Refers
To, like =INDIRECT(D12) or =INDIRECT($D$12).

Now edit your series formula:

=SERIES(Sheet1!$C$18,Sheet1!$D$16:$F$16,Sheet1!Ran geY,1)

There is a wealth of information on the internet about these dynamic
charts. I have some examples and some links on my web site:

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

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


Guackyxxx wrote:

Does anyone know if it possible to have formulas embedded in the SERIES
formula that defines a chart series. For example, what I'd like to have is

=SERIES(Sheet1!$C$18,Sheet1!$D$16:$F$16,INDIRECT(D 12),1)

where D12 is a valid text range.

This does not seem to work. Essentially, I am trying to set up the chart so
I can toggle what is charted without needing to alter the chart references OR
set up a separate set of cells that are charted and that in turn refer to the
(changing) data set I want to chart.

Any thoughts are welcome!