Excel Indirect Function to convert text to Reference

This tutorial explains how to use Excel Indirect Function to convert text to Reference, and how to use in Data Validation.

Excel Indirect Function

Excel Indirect Function is to convert text to Reference (or cell value). For example, formula =A1 returns A1 value because A1 is considered as Reference, but =”A1″ is considered as Text. Excel Indirect Function can convert the “A1″ Text to become Reference.

One popular use of Indirect Function is to use in Data Validation where you convert a Named Range to Reference (see below example).

Another less popular use is that when you combine a Range address such as “A1″ from “A”&”1″, then you want to convert “A1″ to Reference for use in other formula.

Click to see an example that I answered in Microsoft Community.

Syntax of Excel Indirect Function

INDIRECT( ref_text, [A1] )

ref_text	Cell Reference in Text, such as “A1″
[A1]	Optional, reference style Value Description TRUE (default) Relative referencing. For example: A1 FALSE R1C1-style reference For example: R1C1

Example of Excel Indirect Function

For example, A1 contains 1

Formula	Value	Explanation
=INDIRECT(“A1″)	1	Convert text “A1″ into Reference, returning value in A1
=INDIRECT(“$A$1″)	1	Convert text “$A$1″ into Reference, returning value in A1
=INDIRECT(“R1C1″,FALSE)	1	Convert “R1C1″ into Reference, returning value in A1

Convert a Range to Reference

Suppose you want to make a data validation that list values in A1:A3.

First, name the range A1:A3 as data_list

Select Cell B1, click on Data > Data Validation > fill in data as below using Indirect Function to convert text “data_list” to Reference.

Now B1 becomes a drop down box, showing A1:A3 values

Outbound References

https://support.office.com/en-US/article/indirect-function-21f8bcfc-b174-4a50-9dc6-4dfb5b3361cd

Excel Indirect Function to convert text to Reference