Microsoft Office Tutorials and References
In Depth Information
Defining the names
Defining the names
Cell B2, named NumPoints , stores the number of data points to be displayed in the
chart series. The example also uses a linked Spinner to make changing the value of
NumPoints easy.
The chart’s SERIES formula uses two named formulas and, as in the previous
example, these formulas use mixed references. The following names assume that
cell A5 (in the first data row) was active when the names were created.
Category is defined as
=OFFSET(Sheet1!$A5,0,0,NumPoints)
Data is defined as
=OFFSET(Sheet1!$B5,0,0,NumPoints)
When the worksheet is recalculated, the named formulas are updated based on
the active cell.
Using a macro to force a recalculation
To make the calculation occur automatically when the cell pointer is moved, you
can add a simple VBA macro. To add this macro:
1. Right-click the worksheet tab and choose View Code from the shortcut
menu. This activates the VBA Editor and displays an empty code module
for the worksheet.
2. Enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
3. Press Alt+F11 to return to Excel.
Using Check Boxes to
Select Series to Plot
The example shown in Figure 7-15 displays a line chart with three series. The
number of series that are actually displayed is controlled by three Check Box controls.
When all three check boxes are checked, the chart displays data for Product A,
Product B, and Product C. Uncheck a check box and the corresponding series
disappears from the chart.
Search JabSto ::




Custom Search