Dynamic, multiple XY charts

  • Thread starter Thread starter Allan
  • Start date Start date
A

Allan

I have a query that returns weekly data from an external source, covering a
number of years, in tabular form eg.
Year Tonnes Energy
1998 1000 2000
1998 etc. ...
... .... ...
1999 etc
.....
.....
2003 etc

I need to automatically plot a series of Tonnes v Energy for each year on
the same chart.
Note that the number of rows per year is not fixed (@ 52) as there is a
filter on the query .. Tonnes >100 for example.
I recognise I need to define dynamic named ranges for each year but the task
seems beyond me ....... any help would me much appreciated.
Allan
 
Allan -

If your data are in three consecutive columns, and all data for a year
is in a contiguous range, this will work. Put the target year into a
cell somewhere.

Press Ctrl+F3 to open the Define Names dialog. Type the name YearRange
in the Name box, click in Refers To, and select the range containing all
the years.

Type the name TheYear in the Name box, click in Refers To, and select
the cell with the target year.

Enter DataX in the Name box, and in Refers To enter this formula:

=OFFSET(YearRange,MATCH(TheYear,YearRange,0)-1,1,COUNTIF(YearRange,TheYear),1)

Enter DataY in the Name box, and in Refers To, enter this much simpler
formula:

=OFFSET(DataX,0,1)

Now start the chart wizard. Select the chart type in step 1. In step 2,
click on the Series tab. In the X Values (or Category Labels) box, enter
=Sheet1!DataX, and in the Y Values box, enter =Sheet1!DataY (use the
proper sheet name).

When the year in the TheYear cell changes, the chart updates accordingly.

- Jon
 
Thanks for your prompt response, I didn't explain myself very well, it's not
quite what I meant.
I wish to plot multiple series on the same XY scatter chart i.e.
X(1998) v Y(1998)
X(1999) v Y(1999)
X(2000) v Y(2000) etc
As the query is refreshed (perhaps with a different criteria) the number of
rows in total and for each year would change.
I have to define each X & Y series dynamically.
Also, I didn't tell the full story, there will be more than 3 columns and I
will want to produce more than one chart so the variables may not be in
consecutive columns - but they will always be in the same order.
ie Query -> Year - Tonnes - Energy - Electricity - Steam etc
A chart for Tonnes v Energy, Tonnes V Electricity, Tonnes v Steam etc each
containing multiple series for each year.
However your method gives me a few valuable pointers ... I'll keep working
on it.
Thanks again.
Allan
 
Hi Allan -

The technique is the same, just more complicated. Instead of one DataX,
you need X1999, Y1999, X2000, Y2000, X2001, Y2000, etc, one X and Y per
series. You break them out of the list of years the same way. Instead of
offsetting by 1 and 2 for X and Y, you can actually use a MATCH
statement to find which column the intended X or Y caption is in.

You can even set up drop down boxes to facilitate selection of X and Y.
You might find some ideas here:

http://peltiertech.com/Excel/Charts/ChartByControl.html
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

- Jon
 
Back
Top