Microsoft Office Tutorials and References
In Depth Information
Defining the names
Defining the names
For convenience, cell D2 is named FirstRow and cell D4 is named LastRow .
In addition, the workbook has two named formulas. Date is defined as
=OFFSET(Sheet1!$A$2,FirstRow-2,0,LastRow-FirstRow+1,2)
Because the category labels occupy two columns, the OFFSET function uses 2 as
its final argument. In other words, the function returns a range that’s two columns
wide.
Sales is defined as
=OFFSET(Sheet1!$C$2,FirstRow-2,0,LastRow-FirstRow+1,1)
After creating these named formulas, they are then specified as the category
labels and values range for the chart’s series, using the Series tab of the Source Data
dialog box (or by editing the SERIES formula directly). For more information about
using named formulas for a chart series, refer to “Modifying the Series,” earlier in
this chapter.
This technique offers no error handling. For example, entering a
nonnumeric value in cell D2 causes the named formulas to return error values.
Excel displays the rather uninformative error message shown in Figure 7-7.
Figure 7-7: Entering a value that causes an error in the
named formulas results in an error message.
Adding Spinner controls
For additional convenience, you may wish to add Spinner controls to the worksheet
to make it easier to adjust the FirstRow and LastRow values. To do so:
1. Select View
Toolbars
Forms to display the Forms toolbar.
2. On the Forms toolbar, click the Spinner control and then drag in the
worksheet to create the control. You can size and position it just as you
can any other graphic object.
Search JabSto ::




Custom Search