Microsoft Office Tutorials and References

In Depth Information

Dates

The row of dates in row 7 determines which columns of data are

displayed by the remaining formulas in Figure 8-5.

P7: =CurMonth

Contains the date value for the current month. It gets its data from

the CurMonth range in the Control sheet, where you specify the

current month to report. (See Figure 8-4.)

O7: =DATE(YEAR(P7),MONTH(P7)-1,1)

Returns the date value one month earlier.

Copy cell O7 to the left as needed.

Raw Data

This section of Figure 8-5 contains formulas that return data from

your source files.

Column Index Numbers, Actual

The formulas in row 10 in Figure 8-5 return the column index

numbers associated with each date. Here are key formulas for this

section:

P10: =MATCH(CurMonth,ActGL.xls!Date,0)

Finds the current month in the ActGL.xls database, and returns its

column index number.

O10: =P10-1

Returns the index number for the preceding month.

Copy cell O10 to the left as needed.

Row Numbers

Cell B11 in Figure 8-5 returns the row index number used by the

formulas in row 11: