Using a named range as a data source for a chart

[MichaelR]

Hi,

I'm trying to build a chart that that has a named range as its data source.
The named range refers to a table that is 2x3 and is called geographydata. Is
there any way that I can have the source data for the graph to be
geographydata?

Thanks!
Michael

 

[Jon Peltier]

You can enter the name, preceded by the worksheet name and exclamation
point, for the data range, but Excel will convert this name to its cell
address. If you want the chart to be dynamic as the name changes its size,
you have to define a name for each series' data. This will not dynamically
change the number of series in the chart, but it will change the number of
points in a series.

Dynamic Charts:
http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[MichaelR]

Hi Jon,

Thank you for your reply. I was able to put the named range into the data
source by putting the worksheet name first but, as you said, it changes the
named range into a cell reference. Is there any way of preventing this?

There will always be two series in my data but the named range will
sometimes refer just to US and sometimes it will refer to US, CAN, MEX. Thus,
there will sometimes be 3 countries (that are each two columns deep) in my
bar chart and sometimes there will be only 1 country (that is also two
columns deep). If the named reference solution doesn't work, is there any
other way that I could get the chart to adjust from 3 to 1 bars (or vice
versa)?

Thanks!
Michael

 

[Jon Peltier]

There's no way to prevent the conversion of the named range into its cell
address in the data range dialog. However, a simple VBA procedure will
update it:

Sub UpdateDataRange
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("MyRange"), PlotBy:=xlColumns
End Sub

- Jon

 

[MichaelR]

Thank you very much - the sub was hugely helpful!