If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add a data series dynamically to a named range?
Hi everyone
I use Excel 2003 and have two columns of data (Date and Payment) in a dynamic named range which feeds a chart. Everything works fine, but is it possible to extend the idea horizontally so if I add a third column of data (Hours Worked), the dynamic named range extends and the chart accommodates the new data series automatically? I use a bar, colum or line chart so this information could be displayed in this way. Thank you for your time. |
#2
|
|||
|
|||
Add a data series dynamically to a named range?
One dynamic range will not feed a chart. The chart's total data range cannot
be defined dynamically, you have to do it series-by-series. For a series to be defined dynamically, you need one range for X values and another for Y values - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Popeye" wrote in message ... Hi everyone I use Excel 2003 and have two columns of data (Date and Payment) in a dynamic named range which feeds a chart. Everything works fine, but is it possible to extend the idea horizontally so if I add a third column of data (Hours Worked), the dynamic named range extends and the chart accommodates the new data series automatically? I use a bar, colum or line chart so this information could be displayed in this way. Thank you for your time. |
#3
|
|||
|
|||
Add a data series dynamically to a named range?
Thank you Jon.
I realise that one dynamic range is needed for the chart labels and a second one for the series, but can the dynamic range for the data series (Y) be extended automatically to a second (or indeed more) data series (Y2, Y3 ...) in separate columns? The intention would be to have a column or bar graph with columns/bars in blue relating to the first data series, red relating to the second data series and green relating to the third. If I added the columns of data (Y2 and Y3) immediately to the right of the first set of Y data, could the data dynamic range be made to extend automatically one (or more) columns to the right just as it extends automatically down the column as more data points are added? "Jon Peltier" wrote: One dynamic range will not feed a chart. The chart's total data range cannot be defined dynamically, you have to do it series-by-series. For a series to be defined dynamically, you need one range for X values and another for Y values - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Popeye" wrote in message ... Hi everyone I use Excel 2003 and have two columns of data (Date and Payment) in a dynamic named range which feeds a chart. Everything works fine, but is it possible to extend the idea horizontally so if I add a third column of data (Hours Worked), the dynamic named range extends and the chart accommodates the new data series automatically? I use a bar, colum or line chart so this information could be displayed in this way. Thank you for your time. |
#4
|
|||
|
|||
Add a data series dynamically to a named range?
Each Y needs a new range added. This will not occur automatically.
What can be done is to define a range that contains all the rows and columns, and write a macro that uses it for the data range: ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range("theDataRange") Then you can either run this from a button whenever necessary, or hook it up to the Worksheet_Change event. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Popeye" wrote in message ... Thank you Jon. I realise that one dynamic range is needed for the chart labels and a second one for the series, but can the dynamic range for the data series (Y) be extended automatically to a second (or indeed more) data series (Y2, Y3 ...) in separate columns? The intention would be to have a column or bar graph with columns/bars in blue relating to the first data series, red relating to the second data series and green relating to the third. If I added the columns of data (Y2 and Y3) immediately to the right of the first set of Y data, could the data dynamic range be made to extend automatically one (or more) columns to the right just as it extends automatically down the column as more data points are added? "Jon Peltier" wrote: One dynamic range will not feed a chart. The chart's total data range cannot be defined dynamically, you have to do it series-by-series. For a series to be defined dynamically, you need one range for X values and another for Y values - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Popeye" wrote in message ... Hi everyone I use Excel 2003 and have two columns of data (Date and Payment) in a dynamic named range which feeds a chart. Everything works fine, but is it possible to extend the idea horizontally so if I add a third column of data (Hours Worked), the dynamic named range extends and the chart accommodates the new data series automatically? I use a bar, colum or line chart so this information could be displayed in this way. Thank you for your time. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
?-Change data series range as data is entered? | isofuncurves | Charts and Charting | 4 | January 24th, 2006 06:15 PM |
AHHHH-Get Data from Multiple Excel workbooks | JAA149 | General Discussion | 5 | October 30th, 2005 05:19 PM |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
VBA Code problem error 9 | Speedy | General Discussion | 19 | October 15th, 2004 09:05 PM |