Microsoft Office Tutorials and References
In Depth Information
REFER TO A CELL WHOSE ADDRESS VARIES, BASED ON A CALCULATION
The formula in B13 uses concatenation to build a proper label for the cell. The
formula could be:
="Total "&C12 or =CONCATENATE("Total ",C12)
The formula in C13 asks for the SUM of the INDIRECT of the name in C12.
In Figure 25, C12 contains the word COGS . Because COGS is deﬁ ned as C2:
C10, Excel sums the range C2:C10 and returns the answer as the result of the
Figure 25. h e INDIRECT formula returns all the values in a named range.
When someone chooses a new metric from cell C12, the INDIRECT formula sums a
different column. Figure 26 shows the total expenses.
Figure 26. When you change the metric in C12, the formula
totals a dif erent column. h e total in the status bar in the lower
right verii es that the formula is working.