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. * This function can return multiple values. * This function can create a Dynamic Array Formula. * 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 A1style reference, an R1C1style 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. * For the Microsoft documentation refer to support.microsoft.com * For the Google documentation refer to support.google.com 

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. 
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top