Microsoft Office Tutorials and References
In Depth Information
Using INDIRECT to Build and Evaluate Cell References on the Fly
44. Format cell A1 with the custom format mmmm, yyyy. This causes the
date to appear as January, 2015.
55. Right-click the Jan tab name and select Ungroup Sheets.
66. Enter =D2
=D2 as the year-to-date formula in cell D3 of the Jan tab.
77. On the Feb worksheet, build a text formula that returns the name of
the previous month. The quest becomes how to build a formula that
looks like =Jan!D3.
88. Jan is a three-letter abbreviation for any date in the month of Janu-
ary. Therefore, enter a January date in a cell and format the cell
with the custom number format mmm, so that the result is the word Jan.
99. The TEXT function takes a number or date and displays it using a spe-
cific custom number format, so on the February sheet, use
=TEXT(A1,"mmm"), which results in the value Feb. This is close. If
you can find a way to get the name of the previous month, the problem
will be solved.
10. The value in cell A1 is a live date. You can use date math to calcu-
late a different date, such as the date one month earlier. Use the
DATE(year,month,day) function to return a date in the previous month.
For the yearparameter, use YEAR(A1). For the monthparameter, use
MONTH(A1) – 1. For the dayparameter, use DAY(A1). The formula
=DATE(YEAR(A1),MONTH(A1) – 1,1) returns a date that is the first of
the previous month.
11. Combining steps 9 and 10 into a single formula, use
=TEXT(DATE(YEAR(A1),MONTH(A1) – 1,1),"MMM") to return the value
of Jan on the Feb worksheet, Feb on the Mar worksheet, and so on.
12. Use the generic formula
=TEXT(DATE(YEAR(A1),MONTH(A1) – 1,1),"MMM")&"!D3" to build the
13. Select the Feb worksheet. Shift+click the Dec worksheet to place
these 11 worksheets in Group mode. In cell D3, enter this formula:
=INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1) – 1,1),"mmm")&"!D3")+D2.
14. Right-click any sheet tab and select Ungroup to take the workbook out
of Group mode.
The result, as shown in Figure 12.40 , is a formula on the last 11 worksheets
that automatically pulls the year-to-date total from the previous work-
sheet and adds it to the current worksheet total.