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.
* 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 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

 AB
1=INDIRECT("C1") = 60100
2=INDIRECT(B8,FALSE) = 200200
3=INDIRECT("B8",TRUE) = B2300
4=INDIRECT(T(B8)) = B2400
5=INDIRECT(named_range) = B10500
6=INDIRECT("B"&"5") = 500E
7=INDIRECT(B6&"6") = "some text"60
8=INDIRECT("'"&"B12"&'!"&B10) = 'Worksheet Name'!$A$2B2
9=INDIRECT("'["&B13&"]"&B12&'!"&B10) = '[Workbook Name.xls]Worksheet Name'!$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")) = 1500Worksheet Name
13=ROWS(INDIRECT("1:10")) = 10Workbook Name.xls
14=INDIRECT(ADDRESS(5,5)) = RC1 
15=INDIRECT(#REF!) = #REF! 


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