Microsoft Excel - INDIRECT Function
Excel INDIRECT Function and Referencing to the Cell Address
Excel INDIRECT function returns an address when he entered as text, then returns a reference to the specified text string. References are immediately evaluated to display their contents. Use INDIRECT function when you want to change the cell reference in the formula without changing the formula.
- Ref_text is a reference to the cell that contains the reference style A1 reference style R1C1, the name of which is designated as a reference or a reference to the cell as a text string. If ref_text not a valid cell reference, INDIRECT function will display an error value #REF!
- If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT function returns an error value #REF!.
- A1 is a logical value that specifies what type of reference is contained in the cell ref_text.
- If a1 is TRUE or not, is considered ref_text A1 reference style.
- If a1 is FALSE, ref_text is considered the reference style R1C1.
In order to you, understand how it works INDIRECT function, I'll show you the simple Excel examples. Probably someone will ask why I did not put an attachment Workbook in this tutorial. I have not this habit, my opinion is that the user of Excel can best learn when you manually enter data and formulas in the worksheet. Therefore, I suggest, first read this tutorial and if you have interesting and want to learn more, then open your Excel program and follow the step-by-step tutorial, during follow-up manually enter data and formulas in your Workbook.
The first example of Excel INDIRECT function
In the column 'A' we have the data. The cell 'D7' is the number/value of 3. In cell 'C1' have the formula =INDIRECT(A3). So this formula with the INDIRECT function references to the cell 'A3'. The cell 'A3' have a text label 'D7' which is in fact a relative cell address' D7. So the formula is referenced in cell A3, and based on its data pulls the number of the cell that is referenced cells' A3 'and that is the number of cells' D7. (see figure below).
Of course we could have in a cell "C1" to create the formula =D7 but it is not good. Why?
Because in the case of adding a new row above the seventh row our formula back again the same result and that is 3. No, we do not want. We want our formula always returns the result of fixed cells 'D7'. You try Row6 select and insert a new row above. Number/value of 3 will move down and respective formula after inserting new rows will change the formula in 'D8' and the result will be continued to 3. So, when you add a new row all the formulas that are related to the respective range changed their original address. If you use the INDIRECT function with reference to the cell eg. A3, Excel will be added after the row over the reference to the cell that is set to 'A3'. Also, the respective formula =INDIRECT(A3) can be entered as =INDIRECT("D"&7) or =INDIRECT("D7") to avoid referencing the cell 'A3' in this case.
The second example INDIRECT function
In this second example in the figure below, you notice different performance formula using INDIRECT function. If the cell is empty (G5, G6 or G7) formula returns an error "REF!. To avoid this error, use the IF function.
In the cell 'H5' formula is as follows =INDIRECT(G5), the formula returns the data from the cell 'A3'.
In the cell 'H6' formula is as follows =INDIRECT(G6;FALSE), this formula returns the data from the cell 'Row 4 and Column1' => 'A4'.
In the cell 'H7' formula is as follows =INDIRECT("A"&G7), this formula returns the data from the cell 'A9'.
The third example - How to reference the name of the worksheet in the formula that uses the function INDIRECT
In this example, you notice three Worksheets in the image below. On the Worksheet 'Sheet2' is set formula. Column 'A' is a list of worksheets, and in column 'B' are cell addresses from which we display data from the respective worksheets.
The formula in cell 'D1' is =INDIRECT("'"&A1&"'"&B1), this formula can also be entered as =INDIRECT("'"&A1&"'"&"A2")
The formula in cell 'D2' is =INDIRECT("'"&A2&"'"&B2), this formula can also be entered as =INDIRECT("'"&A2&"'"&"F3")
The formula in cell 'D3' is =INDIRECT("'"&A3&"'"&B3), this formula can also be entered as =INDIRECT("'"&A3&"'"&"A1")
All the above formula, based on data from the 'A and B' column/cell pull data from the respective cell references to worksheets. To make it more clearly you see "Evaluate Formula" step by step.
Copying data from multiple worksheets using INDIRECT function
In this example, the INDIRECT function I'll show you how we can copy or display data using one formula from multiple worksheets. In the picture below you see the names of worksheets from which copies data to the 'master' worksheet. All other worksheets are named date of the month. So worksheet '1' defines the first date of the month, Worksheet '2' defines the second day of the month and so on. I am, showed 4 worksheets, of course, one month can have up to 31 days, and so a workbook can contain 31 working list.
In the first Row, the 'master' Worksheet you notice the numbers 1,2,3,4, and may be up to ... 31. These numbers are identical to the names of worksheets for one month containing up to 31 days. Why is it just that you'll see later in the follow-up of this tutorial. Of course, we can have different names and worksheets in the headers, but this example I have imagined this.
First let's see, which's the basic formula for displaying data from another worksheet. You note, in the image below that it is a simple formula that is linked to a specific cell ='1'!D9 (or the formula ='1'!$D9)
So this formula copied data from a worksheet named '1' from the cells 'D9'. This formula is fine if you copy it down. However, a problem occurs when the formula is copied to the right. When you copy the formula to the right, changes to ='1'!E9, ='1'!F9 etc. And returns incorrect result. If we fix the address of the column 'D' (='1'!$D9), the formula returns the result of one and the same. This is not good for our results as alternates or fixed cell address from which you want to pull data. They should in the second row in each column to change all of the formulas and there are thirty-one formula.
To avoid address change cells when copying to the right, we will use the INDIRECT function and create a formula.
The formula above the back data from the cell 'D9' from a worksheet named '1'. Here comes to the fore why I Worksheet named, numbered dates of the month. Because when I copy the formula to the right, I use the data in cells that are found in the headers (Row1) and these are the names of worksheets.
I'll try to explain. So this formula is in cell 'A2', so this =INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9)). This formula can also be entered as =INDIRECT("'"&A$1&"'!$"&"D"&ROW(9:9))
When I copy this formula to the right it will change to the following:
So, note that when copying to the right changes only mark column (header). You do not pay attention to the function ROW(D9) or ROW(9:9) for us it is not important when copying a formula to the right.
Explanation INDIRECT formula
I will try to explain the formula above. On the ribbon "Formulas" tab of the "Evaluate Formula" command. When you are selecting a cell in which the formula and run the "Evaluate Formula" you can see the step-by-step calculation formula.
Copy INDIRECT Formula to the Right
So you see this important part of the formula "'"&A$1&"'!$"
This part of the formula copying the data from the column A and a cell '1'. When you copy the entire formula in the right, the 'A' letter indicates that the column is changed to B, C, D .... By copying the formula to the right we are changing the name of the worksheet and the row remains the same.
A2: "'"&A$1&"'!$" result is 'A$1'!$ => 'A$1'!$D&D9 => '1'!$D9
B2: "'"&B$1&"'!$" result is 'B$1'!$ => 'B$1'!$D&D9 => '2'!$D9
C2: "'"&C$1&"'!$" result is 'C$1'!$ => 'C$1'!$D&D9 => '3'!$D9
D2: "'"&D$1&"'!$" result is 'D$1'!$ => 'D$1'!$D&D9 => '4'!$D9
Of course we still remains the second part of the formula, and that the ROW(D9) or ROW(9:9), which makes the joining of the end of a complete formula calculation. Also note that when copying a formula to the right, change the address of the cell and ROW(D9). This change has no impact on the final formula because it has always defined the ninth row, that is our goal.
Copy INDIRECT formula down
By copying the formula down we change the number of rows, and the worksheet name stays the same.
Such formula by copying down changes the number of rows, column and address of the cell in the first row remains unchanged.
Example for column 'A'
A2: "'"&A$1&"'!$"&"D"&ROW(D9) result is '1'!$D9
A3: "'"&A$1&"'!$"&"D"&ROW(D10) result is '1'!$D10
A4: "'"&A$1&"'!$"&"D"&ROW(D11) result is '1'!$D11
And at the end of the final clarification.
Formula copying to the right, returns the data from the cell D9 from worksheets that have a certain number in the first row (headers).
Formula copying down, returns the data in cell D9, D10, D11 ... from worksheets that have a certain number from headers.
Also you see the tutorial in which the INDIRECT.EXT function that works with closed Workbooks. Please use search box and find MOREFUNC tutorial.