Prerequisite:
Click on the link for
dynamic named ranges
if you aren't already familiar with it, you'll need this information
to proceed.
A challenge arises when you want to use a dynamic range in a chart to
accomodate when the number of items to be graphed changes.
First, let's chart a simple set of monthly values. We'll want to have
the chart automatically extend the data series whenever new months are
added in the source range.
A good way to approach or problem may be to look at what wouldn't
work.
Incorrect Approach #1
What we could NOT do is create a dynamic named range, then use it as
the source data range for the chart. Referring to the illustration above,
we'll create a named range (dynamic or static), Sheet1!rngSource,
then provide it to the ChartWizard as the data range.
However, instead of leaving the range name, the Excel chart converts
the range reference to a static address
(i.e., =Sheet1!$A$1:$B$7 ).
Incorrect Approach #2
The next thing we may try is taking the same formula we used in our
Define Name dialog and entering it directly into the
ChartWizard for the data range.
It will be plotted, but just like in the prior example, it fails to
have the dynamic characteristic for the charted range to adapt to the
varying size of our source range as we add extra data to the table.
How to Make This Work
Now that you understand the challenge. Let's look at a way to tackle it.
The key will be in editing the SERIES formula for the chart.
Do the following:
- Using the ChartWizard, create a ordinary simple line chart to
plot this range ...
=Sheet1!$A$1:$B$7
- Create two dynamic named ranges ...
- Sheet1!rngDate
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
- Sheet1!rngAmt
=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)
Notice :
- The only difference between these two reference formulas is the
OFFSET function's 3rd parameter, for column offset.
- The OFFSET function's 2nd parameter, row offset,
has a value of 1, not zero. You'll offset 1 row down from the anchor
cell ( being $A$1 ), so the header is NOT included.
Next, click on the line plotted on your chart and you'll see a SERIES
in the formula bar.
Let's look at the syntax for this SERIES formula:
=SERIES( name_ref , categories , values , plot_order )
- Name_ref is the name of the data series.
Here it is "AMOUNT".
- Categories is the reference (incl. sheet name)
for the range containing the X-coordinate labels.
Here it's the dates in Column A.
- Values is the reference (incl. sheet name)
for the range containing Y-coordinate values.
Here it will be the dollar amounts in Column B.
- Plot_order is an integer designating if this
series is plotted first, second, third, etc. The chart shown here
just plots a single series, therefore this parameter can only
be 1.
Finally, we need to edit the SERIES formula and replace the
static range addresses for its categories and values
parameters with the dynamic named ranges we defined.
- Replace the categories parameter,
Sheet1!$A$2:$A$7
(2nd argument in SERIES formula), with this reference
Sheet1!rngDate
- Replace the values parameter,
Sheet1!$B$2:$B$7
(3rd argument in SERIES formula), with this reference
Sheet1!rngAmt
Your new SERIES formula should look like this:
=SERIES(Sheet1!$B$1,Sheet1!rngDate,Sheet1!rngAmt,1)
IMPORTANT NOTES:
- Be sure to include the worksheet name in these references
( i.e., Sheet1! ... ).
- The records must be contiguous (that means, without blank rows).
A count of the items in Column A, the Date field,
determines the vertical dimension (the height in rows) of
the dynamic range in this example.
Having done this, you should be able to go to Row 8 of our
example and add Jul-2001 with $ 268 and
you'll see it plotted.
Know an even better way?
Let me know.
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
