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

 Value Description TRUE (default) Relative referencing. For example: A1 FALSE R1C1-style referenceFor 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 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

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist