INDIRECT(ref_text [,a1])

Returns the text string of the contents of a given cell reference.

ref_textThe reference to a range of cells.
a1(Optional) Whether to return A1 or R1C1 reference notation:
True = A1 notation is returned (default)
False = R1C1 notation is returned

Remarks

* For an illustrated example refer to the page under Advanced Formulas.
* This function is volatile 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 can be used to always reference the same cells regardless of whether they are moved or copied.
* For the Microsoft documentation refer to support.office.com

 ABC
1=INDIRECT("C1") = 6010060
2=INDIRECT(C2,FALSE) = 200200B2
3=INDIRECT("C2",TRUE) = B2300"some text"
4=INDIRECT(T(C2)) = B2400$A$2
5=INDIRECT(named_range) = C4500RC1
6=INDIRECT("B"&"5") = 500EWorksheet Name
7=INDIRECT(B6&"6") = "some text" Workbook Name.xls
8=INDIRECT("'"&"C6"&'!"&C4) = 'Worksheet Name'!$A$2  
9=INDIRECT("'["&C7&"]"&C6&'!"&C4) = '[Workbook Name.xls]Worksheet Name'!$A$2  
10=INDIRECT("2:2",TRUE) = 200  
11=INDIRECT("2:2",FALSE) = #REF!  
12=SUM(INDIRECT("B" & "1" & ":" & "B" & "5")) = 1500  
13=ROWS(INDIRECT("1:10")) = 10  
14=INDIRECT(ADDRESS(5,5)) = RC1  
15=INDIRECT(B7) = #REF!  


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top