Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Using a named range as a data source for a chart
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Using a named range as a data source for a chart
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/200...ynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "MichaelR" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Using a named range as a data source for a chart
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" wrote: 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/200...ynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "MichaelR" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Using a named range as a data source for a chart
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "MichaelR" wrote in message ... 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" wrote: 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/200...ynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "MichaelR" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Using a named range as a data source for a chart
Thank you very much - the sub was hugely helpful!
"Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "MichaelR" wrote in message ... 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" wrote: 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/200...ynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "MichaelR" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a named range as a data source for a chart | Excel Worksheet Functions | |||
Named range as chart data reference (error) | Charts and Charting in Excel | |||
Using a Named Range for Data Source In A Chart | Charts and Charting in Excel | |||
Named-range source-data for pie charts on copied worksheets | Charts and Charting in Excel | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel |