The INDIRECT Function For Microsoft Excel Revisited

In a previous article, I discussed some of the limitations of the INDIRECT function in Microsoft Excel and the relative limited scope of use. I would like to revisit this function, however, as there may be some useful situations in which this function can work well. It will not make the function the most useful one, but there are ways to use it to accomplish some simple goals.

To remind my audience, the INDIRECT function is a method to interpret the result from the formula in a cell, which will be in the form of a cell reference, and return the value found in that particular cell. In other words, if your INDIRECT formula returns "C10", the formula goes to cell C10 and shows the value in that cell.

The form of the function is =INDIRECT(cell_reference). The "cell_reference" need to be in quotes if you are directly referring to a cell, as in =INDIRECT("B4"). This will result in displaying whatever value is in the cell B4. So, if 90 is the value in cell B4, 90 will be displayed. There is an interesting twist here to keep in mind, because if you want to return the reference of a reference, then you do not need the quotes. For example, assume that in the cell D4 you have as a value "E4" and in the cell E4 you have the value of 80. The formula =INDIRECT("D4") will return a value of E4, whereas the formula =INDIRECT(D4) will return a value of 90.

While this is not necessarily mindblowing on its own, you can also use the INDIRECT function for a more useful situation, such as returning values on other tabs. Take the example of a company with multiple regional branches that sell similar products. As the CFO or COO, one may want to track the sales per week or month by each branch of a variety of products and have a summary page for quickly determining trends. To keep this simple, let’s assume there are 25 branches and the management team wants to see annual summaries of sales. As long as each tab is setup in a similar fashion, with the names of the products and the resulting sales in the same rows and columns, the INDIRECT function can allow the financial modeler to do some quick copying and pasting to create a summary.

The general formula for finding something on another sheet is =INDIRECT(Sheetx!RC) (as long as the tab has no spaces, no single quotes are necessary, otherwise the formula is =INDIRECT(‘Sheet x’!RC)). Assume that the model is constructed with the branch names across the top in one row and the years are listed in the one column. If the tabs in the master model are named after the respective branch (i.e, West1, West2, Midwest, South1, South2, MidAtlantic, etc.), then the general formula for obtaining the information is =INDIRECT(R1C1&"!R2C2"). More specifically, if the summary page has headers in row 4 beginning in column J, and each of the tabs has the data in cell E4 (on each sheet) that is required, you would have the formula =INDIRECT(J$4&"!E4") and copy across each column (for each branch).

There are some additional ways to use the INDIRECT function, but as was the case in the original article, there are specific instances under which it can be used. For the most part, the average Excel modeler will not need to use this function, but for those with intermediate to advanced knowledge of Excel, I will concede that there actually are a few instances in which this function can be useful.

As a final note, if one needs to incorporate R1C1-style references, the formula is =INDIRECT(cell_reference, false). The second part omitted or true is the normal A1-style reference. In addition, Microsoft Excel 2007 ignores some previous returns that would yield a #REF!, like external references to workbooks not open. The most current version of Excel will yield an answer, so just be careful to note that there are some subtleties not captured in earlier versions of Excel.