INDIRECT

INDIRECT(ref_text [,a1])

Returns the value of a given cell reference specified by a text string.

ref_text	The reference to a range of cells.
a1	(Optional) A logical value indicating whether to return A1 or R1C1 reference notation: True (<> 0) = A1 notation is returned (default) False (or 0) = R1C1 notation is returned

REMARKS

* For an illustrated example refer to the page under Advanced Formulas.
* If you are referencing a different workbook, this workbook MUST BE OPEN, otherwise the function will return #REF!.
* If you want to extract values from a closed workbook, you can use the VSTACK function.
* This function can return multiple values.
* This function can create a Dynamic Array Formula.
* This is a Volatile function and will change everytime a cell on the worksheet is calculated.
* This function will update when the workbook is recalculated by pressing F9.
* The "ref_text" can contain an A1-style reference, an R1C1-style reference, a named range, or a reference to a cell as a text string.
* If "ref_text" is not a valid cell reference, then #REF! is returned.
* If "ref_text" is a reference to a row (eg "3:3"), then the column containing the function is used.
* If "ref_text" is a reference to multiple rows then (eg "2:3"), then the column containing the function is used and an array is returned.
* If "a1" = True, then "ref_text" is interpreted as an A1 style reference.
* If "a1" = False, then "ref_text" is interpreted as an R1C1 style reference.
* If "a1" is left blank, then True is used.
* This function should be used when you want to change the reference to a cell within a formula without changing the formula itself.
* This function will always be single threaded.
* This function can be used to always reference the same cells regardless of whether they are moved or copied.
* You can use the ADDRESS function to return the cell reference given a row and column number.
* You can use the COLUMNS function to return the number of columns in a cell range or reference.
* You can use the ROWS function to return the number of rows in a cell range or reference.
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B
1	=INDIRECT("B1") = 100	100
2	=INDIRECT("B1",TRUE) = 100	200
3	=INDIRECT("R1C2",FALSE) = 100	300
4	=INDIRECT(B8,TRUE) = 200	400
5	=INDIRECT(named_range) = 300	500
6	=INDIRECT("B" & "4") = 400	B
7	=INDIRECT(B6 & "5") = 500	60
8	=INDIRECT("'" & B12 & "'!" & B10) = INDIRECT(" 'Sheet2'!$A$2 ")	B2
9	=INDIRECT("'[" & B13 & "]" & B12 & "'!" & B10) = INDIRECT(" '[Book1.xlsx]Sheet2'!$A$2 ")	"some text"
10	=INDIRECT("2:2",TRUE) = 200	$A$2
11	=INDIRECT("2:2",FALSE) = #REF!	RC1
12	=SUM(INDIRECT("B" & "1" & ":" & "B" & "5")) = 1500	Sheet2
13	=ROWS(INDIRECT("1:10")) = 10	Book1.xlsx
14	=INDIRECT(ADDRESS(5,2)) = 500
15	=INDIRECT("'C:\temp\[Indirect-Test.xlsx]Sheet1'!$A$1") = #REF!

1 - What is the value at the cell reference "B1", using A1 notation.
2 - What is the value at the cell reference "B1", using A1 notation.
3 - What is the value at the cell reference "B1", using the R1C1 notation "R1C2". Cell "B1" is equivalent to row 1, column 2.
4 - What is the value at the cell reference "B2". This uses the value in cell "B8" as the cell reference to use (which is B2).
5 - What is the value at the cell reference "named_range". In this example it refers to cell "B3".
6 - What is the value at the cell reference "B4". This cell reference is created using a string concatenation.
7 - What is the value at the cell reference "B5". This uses the letter "B" from cell B6 to create the cell reference "B6".
8 - What is the value at the cell reference "Sheet2!$A$2". This uses the worksheet name from cell "B12". This uses the cell reference from cell "B10".
9 - What is the value at the cell reference "'[Book1.xlsx]Sheet2'!$A$2". This uses the workbook name from cell "B13". This uses the worksheet name from cell "B12". This uses the cell reference from cell "B10".
10 - What are the values at the cell reference "2:2", using A1 notation. This cell reference actually refers to the entire contents of row 2. This will create a dynamic array formula and can be used to quickly duplicate all the values from another row.
11 - What are the values at the cell reference "2:2", using R1C1 notation. This returns #REF! because the cell reference is in A1 notation.
12 - What is the sum of the values in cell range "B1:B5". This cell reference is created using a string concatenation.
13 - What is the number of rows in the cell reference "1:10".
14 - What is the value at the cell reference "B5". This cell is equivalent to row 5, column 2.
15 - What is the value at the cell reference "A1", on worksheet "Sheet1", in the file "C:\temp\Indirect-Test.xlsx". The file must be open.