Excel INDIRECT function

Summary

The INDIRECT function in Excel is used to convert a text string into a cell reference. This function is incredibly useful for creating flexible cell references that can change dynamically based on the content of another cell. INDIRECT can be used for referencing cells indirectly, creating dynamic ranges, or working with data across different sheets dynamically.
Syntax
				
					=INDIRECT(ref_text, [a1])
				
			
  • ref_text: A text string that represents a cell reference or a range.
  • [a1]: [Optional] A logical value that specifies what type of reference is contained in the ref_text string. TRUE or omitted indicates A1-style reference. FALSE indicates R1C1-style reference.
Return value
Returns the reference specified by a text string.

How to use

Use INDIRECT by providing a text string that represents a cell reference. The function will interpret the text and return a reference to the specified cell or range. This allows for dynamic cell referencing based on the content in other parts of the worksheet.

Examples

Simple INDIRECT
Dynamic Reference to a Cell: To reference a cell dynamically based on another cell’s content:
				
					=INDIRECT("A" & B1)
				
			
Assuming B1 contains the number 5, this formula constructs a cell reference to A5 and returns the value in that cell.
INDIRECT with Named Ranges
Using INDIRECT with Named Ranges for Dynamic Referencing: To create a dynamic reference to a named range based on the selection in another cell:
				
					=INDIRECT(C1)
				
			
If C1 contains the name of a named range, such as “SalesData”, this formula will return the reference to the named range “SalesData”.
INDIRECT Across Worksheets
Referencing Data Across Different Worksheets: To reference a cell in a different worksheet dynamically:
				
					=INDIRECT("'" & D1 & "'!B3")
				
			

If D1 contains the name of a worksheet, say “Sheet2”, this formula constructs a reference to cell B3 on “Sheet2”.

INDIRECT in Data Validation

Creating Dynamic Dropdown Lists with INDIRECT: To create a dropdown list in data validation that changes based on another cell’s value, in the data validation formula field use:

				
					=INDIRECT(E1)
				
			
Assuming E1 contains the name of a range, this data validation rule will create a dropdown list with values from the range named in E1.
INDIRECT for Dynamic Range in SUM
Dynamic Range Summation Using INDIRECT: To sum a range that dynamically changes based on input in another cell:
				
					=SUM(INDIRECT("A1:A" & F1))
				
			

If F1 contains the number 10, this formula sums the range A1:A10. As the value in F1 changes, the summed range adjusts accordingly.

Additional Notes

  • INDIRECT is a volatile function and can cause performance issues in large or complex worksheets.
  • It can be combined with other functions for dynamic lookups, range manipulations, and cross-sheet references.
  • INDIRECT is useful for scenarios where the structure of a spreadsheet changes frequently, allowing formulas to adapt dynamically.

Related Functions

Excel ADDRESS function

The Excel ADDRESS function creates cell addresses from row and column numbers, offering various formats for dynamic references.

Excel OFFSET function

The Excel OFFSET function creates dynamic ranges by returning references shifted from a start point by specified rows and columns.

Content Navigation