Set chart source data to a named range - how?
James Radke
I must be missing something.. I can't seem to set the source data for a
chart to a named range.. is there some trick to this? What do I enter in
the Values area?
I am assuming I can then change the named range with a macro, and the chart
will then reflect the new named range.. correct?
Thanks!
Jim
James Radke
the first post...
I have three template sheets.. each identical but with different period
data...
I want to be able to select which period's data is put on the chart......
So, if the cells F2 through F11 on sheets 1, 2, and 3 are the data I would
like to be mapped...
So, how can I set up a named range / chart so that the range first points to
Sheet1!F2:F11... then if I change the named range to Sheet2!F2:F11 I would
like the chart to reflect the new data.....
Thanks!
Jim
"James Radke" <jra...@wi.rr.com> wrote in message
news:#03lBOR0BHA.2036@tkmsftngp03...
Bernard Liengme
Make the chart with the data from Sheet1
Right click on chart, select Source Data from popup menu
Open Series tab and click the Add button
Use the pointing method to show the y-values (ie, open sheet2, and drag over
the value)
The dialog box will show =Sheet2!F2:F11
If you are using legend, remember to point to cell for Name also
...what this world needs is software to <<quickly>> make movies; it takes
longer to type this than it does to do it!!!!
best wishes
Bernard
Jon Peltier
You can set the source range to a named range, but you've already discovered
that Excel changes the named range to its static address equivalent. Bummer.
You can get around this two ways. The first is to define individual named
ranges the X and Y ranges for each plotted series. You can see examples of
this if you check the archives (groups.google.com) for dynamic charts and
dynamic ranges. Then you apply these names to each series in the Series tab
of the Source Data dialog. Takes a little longer, but it works.
The other way needs a little VBA. If your named range is MySourceData, this
macro will update the chart, setting its source data to MySourceData:
Option Explicit
Sub UpdateChart()
Dim rngSourceData As Range
Set rngSourceData = Range("MySourceData")
ActiveChart.SetSourceData _
Source:=rngSourceData, _
PlotBy:=xlColumns
End Sub
When your named range has changed, select the chart and run this macro. You
could link this macro to a worksheet_change event, if you want to make it
happen automatically.
- Jon
-------
Jon Peltier, Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <edrJ2YR0BHA.2664@tkmsftngp03>, James Radke said...
Tushar Mehta
email copy to James Radke.
Please use the newsgroup for further discussion.]
I interpreted your problem very differently from Jon or Bernard.
Suppose on the worksheet that contains the chart, you specify which data
sheet you want to use in cell C2. So, if the data are in sheets named
Sheet1, Sheet2, and Sheet3, cell C2 will contain the text value
'Sheet1' or 'Sheet2' or 'Sheet3.'
Now, define a name such as
chartRng =INDIRECT("'"&Sheet1!$C$2&"'!A1:A3")
Plot this. Now, if you change the contents of C2, the graph will adjust
automatically.
For more on how to use a named range in a chart, check the tutorial
'Dynamic Charts' of my web site.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <#03lBOR0BHA.2036@tkmsftngp03>, James Radke <jra...@wi.rr.com> wrote
