Exceljet

Quick, clean, and to the point

Excel INDIRECT Function

Excel INDIRECT function
Summary 

The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.

Purpose 
Create a reference from text
Return value 
A valid worksheet reference.
Syntax 
=INDIRECT (ref_text, [a1])
Arguments 
  • ref_text - A reference supplied as text.
  • a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.
Usage notes 

Use INDIRECT to create or supply a reference in text form. Indirect is useful when you want to convert a text value into a valid cell reference.

The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula =INDIRECT("A1:A100") will always refer to the first 100 rows of column A, even if rows in that range are deleted or inserted.

  • References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
  • When ref_text is an external reference to another workbook, the workbook must be open.
  • a1 is optional. When omitted, a1 is TRUE = A1 style reference.
  • When a1 is set to FALSE, INDIRECT will created a an R1C1-style reference.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.