Returns a reference, given a text string.
- textref is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.
- The reference may then be used in formulas or functions requiring a reference - see the examples below.
- type is an optional number; if 0, textref is taken to be in R1C1 notation, and otherwise (or if omitted) textref is taken to be in the usual A1 notation.
- A named reference is recognised with either type.
- (in a cell) returns the contents of cell B2. INDIRECT("B2") returns a reference to cell B2, so this is the same as =B2.
=INDIRECT("B" & "2")
- also returns the contents of cell B2. "B" & "2" becomes the text string "B2".
- where cell D3 contains the text B2 returns the contents of cell B2.
- returns the sum of the numbers in A1:C3.
- returns the contents of cell B2. The type is non-zero, so B2 is evaluated as the usual A1 notation.
- returns the contents of cell C2. The type is zero, so R2C3 is evaluated as R1C1 notation.
- entered in cell B1, returns the contents of cell C4. The type is zero, so RC is evaluated as R1C1 notation, in this case relative to cell B1: 3 rows down and 1 column across to yield a reference to cell C4.
- Excel represents some cell references differently to Calc, so this function is not always portable. For example INDIRECT("Sheet2.A1") is valid in Calc, but in Excel the required form is INDIRECT("Sheet2!A1"). A portable solution might be INDIRECT(ADDRESS(1;1;4;"Sheet2")). In the case of R1C1 notation only, Calc uses the ! character to separate the sheet identifier.
- The type parameter is implemented from OOo3.0 - see Issue 91020.
- In Excel, type is a logical value; Calc also accepts logical values but they are displayed as numbers.
- Named cells/ranges are recognised in Calc from OOo2.4 - see Issue 4695.