Dynamic Charts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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/DynamicChartLinks.html]
HTH,
Henk

bjw said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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/DynamicChartLinks.html]
HTH,
Henk

bjw said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top