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 Excel 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
|
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 Excel 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