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

excel_indirect

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

excel_indirect_01

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

excel_indirect_02

Outbound References

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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

Leave a Reply

Your email address will not be published. Required fields are marked *