Microsoft Office Tutorials and References
In Depth Information
=SERIES(,Sheet1!MonthRange,Sheet1!SalesRange,1)
The example in this section, named plot last n data points.xlsx, is available at this
book's website.
Choosing a start date and number of points
This example uses dynamic formulas to allow the user to display a chart with a specified number of data points,
beginning with a selected starting date. Figure 17-31 shows a worksheet with 365 rows of daily sales data.
The start date is indicated in cell E2 (named StartDay ), and the number of data points is specified in cell G2
(named NumDays ). Both cells use data validation to display a drop-down list of options.
What makes this work is two named formulas. The formula named Date is
=OFFSET(Sheet1!$B$2,MATCH(StartDay,Sheet1!$B:$B,1)–2,0,NumDays,1)
The formula named Sales is
=OFFSET(Sheet1!$B$2,MATCH(StartDay,Sheet1!$B:$B,1)–2,1,NumDays,1)
Search JabSto ::




Custom Search