Login below to access our online services for members, students and business partners.
Some modellers swear by it, some swear at it. This month we look at an Excel function that frequently polarises modellers: INDIRECT. By Liam Bastick, managing director with SumProduct Pty Ltd.
QueryMy colleagues have suggested I would benefit from using the INDIRECT function in my models. I am not sure when I would use it. Could you give me some tips please?
AdviceExcel’s INDIRECT function allows the creation of a formula by referring to the contents of a cell, rather than the cell reference itself.
The INDIRECT(ref_text,[a1]) function syntax has two arguments:
Essentially, INDIRECT works as follows:
In the above example, the formula in cell H18 (the yellow cell) is
With only one argument in this function, INDIRECT assumes the A1 cell notation (e.g. the cell in the third row fourth column is cell D3). Note that the value in cell H11 is H13, this formula returns the value / contents of cell H13, i.e. 187.
This idea can be extended: the value indirectly referred to does not need to be in the same worksheet (or even workbook) as follows.
Example Referring to Another Worksheet
The formula in the yellow-coloured cell (H17) uses concatenation (please see September 2010’s article):
This formula is difficult to read and I have therefore coloured the vital apostrophes (’) in red to try and make the formula clearer. H11 in my example is Sum_First_Ten_Rows_BA, which is the name of another worksheet in the workbook and F29 is the cell to be linked to. In other words, this formula becomes
which will make more sense to end users. The point is, however, the value in cell H11 can be changed so that the formula suddenly links to a completely different worksheet.
Eagle-eyed readers may note that worksheet names without spaces do not need apostrophes. Whilst this is true, I include them here so that the formula will work in general.
Advantages of INDIRECT
INDIRECT has useful properties that may be exploited. For example, consider the following illustration:
Summing the First Ten RowsImagine you wanted to sum the first ten values in this list. The obvious formula to use would be
However, what happens if someone inadvertently inserts or removes rows in this range? If a row were to be inserted the formula would automatically update to =SUM(F11:F21), which most of the time would be what would be required. On occasion, though, it might be important that only the first ten values are still summed. INDIRECT can ensure this happen, viz.
Note that when a reference is typed in like this it should be included in inverted commas as displayed. Using this formula will maintain the integrity of the referencing as required (please try it for yourself in the attached Example file).
So far, all of my examples use the A1-style of cell referencing. However, using the R1C1 (row / column approach where the third row fourth column would be (3,4)) has benefits too. When this method is used, I often use INDIRECT in conjunction with the ADDRESS function.
The ADDRESS(row_num,column_num) function syntax has the following arguments:
Therefore =ADDRESS(3,4) is $D$3.
R1 C1 IllustrationJust using INDIRECT in the example above, cell H29 uses concatenation once more:
The second argument (FALSE) is necessary to recognise the R1C1 notation. Here, this formula reduces to =R22C9, which is the 22nd row, ninth column, i.e. delivers the value in cell I22 (highlighted in red).
This formula can be difficult to understand for the uninitiated. I am not saying the following is necessarily simpler, merely it is an alternative. The second formula in cell H31 is
This does not need the R1C1 notation as ADDRESS(H11,H12) equals cell $I$22.
Another advantage I would like to mention is in generating dynamic data validation lists. You may recall I discussed data validation in April’s article.
Again, this example can be found in the attached Excel file:
Dynamic Data Validation ListsHere, I want to select a classification category in cell G28, based on the financial statement I select in cell G27 (e.g. Balance Sheet -> Current Assets).
The trick here is not to include spaces in the names of the financial statements (see October 2011’s article for more details).
Then, first of all, in my illustration above, I have named cells F12:F22 Income_Statement, cells G12:G19 Balance Sheet and cells H12:H15 Cash_Flow_Statement. Cells F11:H11 have been used to construct a data validation list in cell G27 and then the data validation list in cell G28 has used the INDIRECT function in the ‘Source:’ field as follows:
Data Validation List using INDIRECT
As a different financial statement is selected in cell G27, so the list will update in cell G28 (but only once the data validation list is activated, which is an Excel limitation).
One last – and key – example: how often do you seek a summary sheet which selects data from one of several similarly constructed datasheets? I have seen all sorts of weird and wonderful formulae to perform this common requirement, but INDIRECT is by far one of the simplest approaches available.
Consider the following file, which has several similar worksheets:
I might require a summary sheet:Summary SheetIn my example, I have called my similar worksheets Guns_BA and Drugs_BA. The BA here refers to “Blank Assumptions” but it could mean “Basically Anything”, i.e. the worksheet names contain more than just the business unit name.
With cell H9 named Selection, the formula used in the calculations is simply
(I have again coloured the necessary apostrophes in red in this above formula). However, as well as apostrophes and concatenation this formula uses a neat trick. The second argument must be FALSE (i.e. the formula assumes the R1C1 notation). When this is selected the RC in the above formula means use the row and column reference of the cell this formula is in. This avoids unnecessary hard code and generates a formula that changes reference depending upon the formula location in the worksheet. For example, in cell G12, the formula reduces to =’Guns_BA’!G12 and in cell J21 it reduces to =’Guns_BA’!J21, etc.
Disadvantages of INDIRECT
Not all modellers embrace this useful function. There are three key issues with INDIRECT:
Ultimately, the use of INDIRECT becomes a “horses for courses” issue. If modellers are knowledgeable and wary of its limitations, INDIRECT can simplify and resolve many common modelling problems. Just be careful out there.
If you have a query for the Spreadsheet Skills section, please feel free to drop Liam a line at firstname.lastname@example.org or visit the SumProduct website