Dynamic Charts

I am trying to create a dynamic line chart. The data in column A is dates
and the data in the header row (location) would be the series for the chart.
The actual data is units. Problem is, more dates could be added as well as
more locations. Is there any way to name the ranges to account for more or
less data on both location and date?

Hi:
I repeat my answer to another question here again.

There is an elegant solution, called "dynamic named range", that works
with the OFFSET command. Search the web with this jargon, or check out Jon
Peltier's website; it contains links to many others where you find
instructions how to do it.
[http://peltiertech.com/Excel/Charts/...artLinks.html]
HTH,
Henk

"bjw" wrote:

> I am trying to create a dynamic line chart. The data in column A is dates
> and the data in the header row (location) would be the series for the chart.
> The actual data is units. Problem is, more dates could be added as well as
> more locations. Is there any way to name the ranges to account for more or
> less data on both location and date?

I understand how to create named ranges and the OFFSET function. In order to
capture all the coulmns that could possibly be added to the report, i have to
create named ranges for all of the blank columns, add them to the report and
then when they are populated, they will be visible in the chart? Or is there
another way to do it? Loc5 and Loc 6 and so on could be added at a later date
and Date4 and Date 5 could be added...The dates i have under control...I am
just wondering if i have to create 10-15 named ranges for the locations that
have not been added yet?
Loc2 Loc3 Loc4
Date1
Date2
Date3

"HEK" wrote:

>
> Hi:
> I repeat my answer to another question here again.
>
> There is an elegant solution, called "dynamic named range", that works
> with the OFFSET command. Search the web with this jargon, or check out Jon
> Peltier's website; it contains links to many others where you find
> instructions how to do it.
> [http://peltiertech.com/Excel/Charts/...artLinks.html]
> HTH,
> Henk
>
> "bjw" wrote:
>
> > I am trying to create a dynamic line chart. The data in column A is dates
> > and the data in the header row (location) would be the series for the chart.
> > The actual data is units. Problem is, more dates could be added as well as
> > more locations. Is there any way to name the ranges to account for more or
> > less data on both location and date?

Create one dynamic name for Dates. Allow the user to select the column to
plot by placing the name into a cell. You can make this fancier and more
robust later with a dropdown control. I'll assume your data is in A3:D10
(dates in A4:A10, "Loc2" to "Loc4" in B3:D3). The user for now types the
column name he wants in B1 (e.g., "Loc3"). To know which column should be
plotted, use MATCH(B1,B3:D3,0) in a new dynamic name definition:

Name: RangeToPlot
Refers To:
=OFFSET(Dates,0,MATCH(B1,B3:D3,0))

Now set up your chart series to use Dates for X and RangeToPlot for Y.

Having written the description above, I realize there's already an example
using a combo box on my web site:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"bjw" <[email protected]> wrote in message
news:[email protected]...
>I understand how to create named ranges and the OFFSET function. In order
>to
> capture all the coulmns that could possibly be added to the report, i have
> to
> create named ranges for all of the blank columns, add them to the report
> and
> then when they are populated, they will be visible in the chart? Or is
> there
> another way to do it? Loc5 and Loc 6 and so on could be added at a later
> date
> and Date4 and Date 5 could be added...The dates i have under control...I
> am
> just wondering if i have to create 10-15 named ranges for the locations
> that
> have not been added yet?
> Loc2 Loc3 Loc4
> Date1
> Date2
> Date3
>
> "HEK" wrote:
>
>>
>> Hi:
>> I repeat my answer to another question here again.
>>
>> There is an elegant solution, called "dynamic named range", that works
>> with the OFFSET command. Search the web with this jargon, or check out
>> Jon
>> Peltier's website; it contains links to many others where you find
>> instructions how to do it.
>> [http://peltiertech.com/Excel/Charts/...artLinks.html]
>> HTH,
>> Henk
>>
>> "bjw" wrote:
>>
>> > I am trying to create a dynamic line chart. The data in column A is
>> > dates
>> > and the data in the header row (location) would be the series for the
>> > chart.
>> > The actual data is units. Problem is, more dates could be added as
>> > well as
>> > more locations. Is there any way to name the ranges to account for more
>> > or
>> > less data on both location and date?