Thread: 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(D12),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!RangeY,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(D12),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!RangeY,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(D12),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!
>