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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>>