Microsoft Office Tutorials and References
In Depth Information
Using INDIRECT to Build and Evaluate Cell References on the Fly
pear for some number of rows, starting in row 7. Each worksheet has a total
for the month in cell D2.
Figure 12.40. Cell D4 dynamically builds a text formula to reference the
Cell D4 dynamically builds a text formula to reference the
previous sheet, and then
previous sheet, and then INDIRECT
INDIRECT evaluates the formula.
evaluates the formula.
In this example, you want to add a year-to-date total in cell D3 on each
worksheet. This is fairly difficult to do without VBA. Many VBA books in-
clude a user-defined function to describe the previous sheet in a workbook.
However, this function fails if you send the workbook to someone who dis-
ables macros on her computer. Instead, you can solve this problem with the
clever use of text functions and the INDIRECT function. To do so, follow
11. Select the Jan worksheet.
22. Shift+click the Dec worksheet to put all 12 sheets in Group mode.
33. In cell A1, enter the formula =A7
=A7. This adds the first date as a title
for the worksheet.