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

formula.

Part

I

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.