INDIRECT

Formulas / INDIRECT
Uses text to create references
INDIRECT(ref_text, [a1])
  • ref_text - The text with a reference in it
  • a1 - [OPTIONAL] A boolean argument indicating whether A1 or R1C1 style reference should be used

Examples

  • =INDIRECT("A1")

    The INDIRECT function returns a reference as =A1. This means that it provides a reference to a specific cell in the spreadsheet, like the A1 cell. This can be helpful when you need to reference the same cell multiple times and don’t want to type out the reference every time. It is also useful for creating dynamic references.

  • =INDIRECT("R1C1",FALSE)

    The INDIRECT function returns a reference as =R1C1, which is a special type of reference that uses only numbers to specify the row and column in the spreadsheet. This is helpful if you have a large number of columns and don’t want to use alphabetic references. It is also useful if you are trying to reference a cell in a different sheet.

  • =INDIRECT(B5&"!A1")

    The INDIRECT function can also be used to concatenate the text in B5 with "!A1", creating a reference to the correct cell in the spreadsheet. This is helpful if you are looking to reference information from multiple sheets in a single formula.

Summary

The INDIRECT function allows users to create a reference to a cell given a text string, without having to manually change the cell references in the original formula.

  • The INDIRECT function returns a valid worksheet reference and may be useful for creating text values for use as a worksheet reference.
  • However, INDIRECT is not recommended for large or complex worksheets. It is a volatile function, meaning INDIRECT can cause performance issues in large or complex worksheets and evaluates references it creates in real time.
  • Note that INDIRECT displays the content of its reference.


Frequently Asked Questions

What does the INDIRECT function do?
The INDIRECT function returns a reference to a range. It does not evaluate conditions or logical tests, nor does it perform calculations.
How does the INDIRECT function work?
The INDIRECT function locks a given cell in a formula, allowing the formula to remain the same even when the cell references in the formula change. The INDIRECT function works with A1-style references and R1C1-style references, and references do not change when new rows or columns are added to a worksheet, or when existing rows or columns are deleted.

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.