Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display multiple series in a pie chart? | Charts and Charting in Excel | |||
How to change Series order in a Combination Chart? | Charts and Charting in Excel | |||
How to change Series Order in a Combination Chart? | Excel Discussion (Misc queries) | |||
Dynamic series in Chart | Charts and Charting in Excel | |||
Help! I'm losing one series of data in an overlay chart. | Charts and Charting in Excel |