|
This tutorial shows you how to make a dynamic and animated XY trend chart. It is based on the example in Chrt_XY_Trend_Animation.xls.
1. Charting Dynamic Ranges
Dynamic chart names provide a powerful way to automatically update chart ranges as more data is added to your source data table.
In this example we show how to use dynamic chart ranges to animate a chart. We start with time series data in Cols A&B. We next add dynamic range names for the data to be plotted. See cols D & E in data table.
We can create dynamic range names for our plot variables with tools > Name > Define > "Refers to" formulas as follows:
Plot DT = Offset(Data!$D$2,0,0,Counta(Data!$D2:$D$10000))
Plot Value = Offset(Plot_DT,0,1)
2. Dynamic Chart
Using the Chart Wizard, we can enter our dynamic range names right into the X and Y values definition boxes. Notice that we must enter the sheet name with an "!" or the workbook name with an "!" before the range name.
We now have a dynamic chart that will plot data in the Plot_DT and Plot_Value range names.
Next we need to get source data into these range names so that our plot will show data.
3. Copying Source Data to Dynamic Ranges
We use a simple VBA procedure to copy data from the source range to the Plot_DT and Plot_value dynamic ranges. See the download workbook for the VBA code.
4. Working Example
The working example in Chrt_XY_Trend_Animation.xls allows the user to turn the animation on/off and to generate a time report if desired. The user can clear previous charts and regenerate chart over and over.
|