The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.

Syntax

INDIRECT(ref_text,a1)

** ref_text**: a text that contains a cell reference.

**a1**: a logical value that specifies what type of reference is contained in the cell ref_text.

• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12

• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3

Example 1:

Cell B1 contains the formula: =INDIRECT(A1)

Cell A1 contains the text: “C6”

Cell C6 contains the value: 135

The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.

If you change the text in cell A1 to “C3” then you will get 198 in B1.

Example 2:

The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.

Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)

Cell A1 contains the text: “Sheet2”

Cell C8 contains the value: 1922

The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.

If you change the text in cell A1 to “Sheet1” then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.

I am not new to blogging and actually value your web site. There is much innovative content that peaks my interest. I am going to bookmark your site and keep checking you out.

Thanks for your interest.

Wow…!!!

Nice explanation.

Thanks

Very simple & useful explanation to understand this function.Thanks

Thanks.

I am glad that I made myself clear.

Just wondering if you know of an alternative to INDIRECT for using in a formula to pull data from another worksheet? I love INDIRECT, but when you need to use is a large number of times in multiple worksheets, it really bogs down your system constantly recalculating.

See this post:

http://www.exceldigest.com/myblog/2008/04/05/how-to-link-to-values-in-another-worksheet-workbook/

Thanks for the post. it was really useful for me. Can you explain further uses of Indirect function

Check This article. It may be helpful.

Can someone tell me whats wrong with this formula.

=IF(C104,SUMIF(Data!L2:L10468,C11,INDIRECT(“Data!”&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”2″&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”10468”)),SUM(INDIRECT((“Data!”&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”2″&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”10468”))))

Please, what are you try to get from this complex formula and what kind of error did you get.