1.5 Indirect Referencing Using Names

When you refer to a cell in a formula, Excel will either get the value of that cell or if the cell has a formula, it will evaluate the formula in that cell and return the value to be used in the current formula. Generally, that is what we want to do. However, sometimes you might want to refer to a cell, and rather than use its value, you would like to treat it as a reference. That’s the idea behind indirect referencing. This is especially helpful in creating dynamic spreadsheets that allow user input.

Consider a scenario where we have a list of months, and we want to get all the sales for all regions for that month. While we could write a formula to calculate each month individually (e.g., =SUM(January) ), it could be useful to allow the user to specify the name of the month they would like to calculate the total for. In this case, we can use indirect referencing by using the INDIRECT formula.

This formula gets the value in C55 and then treats it as a cell range because the INDIRECT function is used. Thus, it sums all the values in the June named range. Now, when we want to get the total for another month, we can simply type the name of the month (e.g., July) in C55, and the total for that month will be displayed in D55.