Christine -
Making a dynamic chart is not too complicated, but you can't just read
about it, you have to work through a couple examples. I have a couple
examples here, and links to more examples as well:
http://peltiertech.com/Excel/Charts/Dynamics.html
You need a few formulas to do this. If you know where your data starts,
the formulas are easier. Defining a name merely means applying a name to
a range of cells, and you can use a formula for this.
If you know the first cell with data, the formula is easier. Say the
monthly data starts in E5, and extends down column E. Press CTRL+F3 to
open the Define Names dialog (or go to Insert menu > Names > Define).
Enter a name for the first cell, MyFirst, click in the Refers To box,
then select E5 with the mouse. The Refers To box now says =Sheet1!$E$5.
Click Add, and you've created your first named range.
If you know it's column E but aren't going to know the first cell, this
formula defines MyLast as the first numeric cell in the column:
=INDEX(Sheet1!$E$1:$E$65535,MIN(IF(ISNUMBER(Sheet1!$E$1:$E$65535),ROW(Sheet1!$E$1:$E$65535),65536)),1)
It means (look up INDEX in the help files) find within the range
Sheet1!$E$1:$E$65535, the cell in the row defined by
MIN(IF(ISNUMBER(Sheet1!$E$1:$E$65535),ROW(Sheet1!$E$1:$E$65535),65536))
and in the column 1. That bit inside the min returns an array (list) of
numbers, which is equal to the row of the cell if it contains a number,
or is equal to 65536 if it doesn't contain a number. (so above the table
there can't be any numbers in column E). In the example I gave, where
the data starts in cell E5, this array is {65536, 65536, 65536, 65536,
5,6,7,8, etc}. The first four cells are non-numeric, so the first four
numbers are 65536. There are numbers in E5, E6, etc, so the row numbers
are 5,6, etc. The min of the list is 5. Index(E1:E65535,5,1) then means E5.
Now you need a formula for the last cell. Enter MyLast in the Name box,
and the refers to formula is
=INDEX(Sheet1!$E$1:$E$65535,MAX((Sheet1!$E$1:$E$65535>0)*ROW(Sheet1!$E$1:$E$65535)),1)
This finds the last cell with a positive value in column E.
Let's combine MyFirst and MyLast into a formula for the entire range of
cells they span. In the name box, enter the name MyValues, and in the
Refers To box, enter a much simpler formula:
=MyFirst:MyLast
Of course, you could combine the two previous formulas into a single
monster formula for MyValues:
=INDEX(Sheet1!$E$1:$E$65535,MIN(IF(ISNUMBER(Sheet1!$E$1:$E$65535),ROW(Sheet1!$E$1:$E$65535),65536)),1):INDEX(Sheet1!$E$1:$E$65535,MAX((Sheet1!$E$1:$E$65535>0)*ROW(Sheet1!$E$1:$E$65535)),1)
That's your defined name for the Values. To define a name for the
months, enter the name MyMonths, then enter a simple formula:
=OFFSET(MyValues,0,-1)
This means define a range the same size as MyValues, located down 0 rows
and right -1 rows (or left 1 row). I assume the months are in column D
next to the values.
Now to make the chart. Click the chart wizard button, or find Chart on
the Insert menu. In step 1, select the chart type. In step 2, click on
the series tab. In the Name box, type the name of the series (the legend
entry for the series), or click in it and select the cell with the
mouse. Clear the Y Values box, and enter =Sheet1!MyValues, referring to
the monster defined name from above. In the X Values (Category Labels)
box, enter =Sheet1!MyMonths. Continue to the end, and you will have your
first dynamic chart.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
