Excel INDIRECT Function
The Excel INDIRECT function returns a reference based on a text string
Example: Excel INDIRECT Function
|
=INDIRECT("C7")
|
Result in cell E5 (1247) - returns the value in cell C7. Given we have used double quotation marks around C7 the formula treats this value as text.
|
|
=INDIRECT("C7",TRUE)
|
Result in cell E6 (1247) - returns the value in cell C7. Given we have used double quotation marks around C7 the formula treats this value as text. We have also specified TRUE for the A1 reference style which lets the function know that the text string is written in A1 format.
|
|
=INDIRECT("INDIRECT!C7")
|
Result in cell E7 (1247) - returns the value in cell C7 in a sheet named "INDIRECT".
|
|
=INDIRECT("R7C3",FALSE)
|
Result in cell E8 (1247) - returns the value in cell C7 based on the R1C1 reference style. Given we have used double quotation marks around R7C3 the formula treats this value as text. The text string is written in the R1C1 format and therefore we have stated FALSE for the A1 reference style.
|
METHOD 2. Excel INDIRECT function using the Excel built-in function library
EXCEL
Formulas tab > Function Library group > Lookup & Reference > INDIRECT > populate the input boxes
| =INDIRECT("C7",TRUE) Note: in this example we are returning the value in cell C7 given the C7 in the formula is treated as text as we have used the double quotation marks. We have also specified TRUE for the A1 reference style which lets the function know that the text string is written in A1 format. |
 |
Sub Excel_INDIRECT_Function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("INDIRECT")
'apply the Excel INDIRECT function
ws.Range("E5").Formula = "=INDIRECT(""C7"")"
ws.Range("E6").Formula = "=INDIRECT(""C7"",TRUE)"
ws.Range("E7").Formula = "=INDIRECT(""INDIRECT!C7"")"
ws.Range("E8").Formula = "=INDIRECT(""R7C3"",FALSE)"
ws.Range("E5").Formula = "=INDIRECT(""C7"")"
ws.Range("E6").Formula = "=INDIRECT(""C7"",TRUE)"
ws.Range("E7").Formula = "=INDIRECT(""INDIRECT!C7"")"
ws.Range("E8").Formula = "=INDIRECT(""R7C3"",FALSE)"
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named INDIRECT.
Worksheet Name: Have a worksheet named INDIRECT.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5"), ("E6"), ("E7") and ("E8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
DESCRIPTION
The Excel INDIRECT function returns a reference based on a text string.
The Excel INDIRECT function returns a reference based on a text string.
SYNTAX
=INDIRECT(ref_text, [a1])
=INDIRECT(ref_text, [a1])
ARGUMENTS
ref_text: (Required) A cell reference represented as text.
a1: (Optional) Specifies what type of reference style to use. This can be any of the following:
| Value | Explanation | Example |
|---|---|---|
| TRUE | A1 reference style | A1, A2, B2 |
| FALSE | R1C1 reference style | R1C1, R2C1, R2C2 |
Note: If the a1 argument is omitted, the default option is TRUE (A1 reference style).