Indirect Explanation

[Arts]

Hi all

I have been trying to understand the INDIRECT concept but after hours of reading various web links and seeing examples I seemed to be more confused. If anyone could help shed some light on the questions I have that would be most appreciated. The issue I am having is not knowing when to use " " when referencing a cell.

From the below example if I need to attain the text East using the INDIRECT function I would need to wrap the I3 in quotations.

Now when I have named range "west" example below : when summing the numbers I am not clear as to why I do not need to wrap the D2 in "" so the formula in my mind should be =SUM(INDIRECT("D2")) as I need to retrieve the text "west" as I did with "East" above. But when the formula is entered this way this seems to not work but am following the rationale of attaining the text by using ""

Also when I type in west within the formula this is written as "west" : =SUM(INDIRECT("West")) but when referencing West if this was in a cell like East I would need to use (INDIRECT("D2") which would give me "West" as it did with East to attain the text but for what ever reason the formula needed is =SUM(INDIRECT(D2)) and not =SUM(INDIRECT("D2"))

Any help on this would be most helpful as when I am seeing multiple videos on this the first thing that is mentioned is that in order to attain the value in the as text we must use parenthesis.

And when watching video examples of Indirect formula being used with vlookup for example it seems when parenthesis should be used they aren't and vice versa.

If someone could break this down for me that would be very helpful.

Thanks

Arts

 

[GraH]

Here is my attempt to explain as simple as possible.
As any formula function, leaving a few exceptions aside, it takes cell references as argument. However that argument should be text. As described on the support page.

Support page

INDIRECT function​

This article describes the formula syntax and usage of the INDIRECT function in Microsoft Excel.

Description​

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax​

INDIRECT(ref_text, [a1])
The INDIRECT function syntax has the following arguments:

• Ref_text Required. A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
  • If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
    
    Note External references are not supported in Excel Web App.
  • If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
    
    Note This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.
• A1 Optional. 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.
  • If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		B5	Sales	Sales is a named range
3
4
5		7	4
6			5
7			6		7	4	4	7
8			7			5	5
9			8			6	6
10			9			7	7
11			10			8	8
12						9	9
13						10	10
14
15
Sheet1

Cell Formulas
Range	Formula
E7,F7:F13	E7	=INDIRECT(B2)
G7:G13	G7	=INDIRECT("Sales")
H7	H7	=INDIRECT("JustANumber")
Dynamic array formulas.

So the formula in E7 returns 7 because it reads the text in cell B2 (and that's text). That text being a cell reference B5. B5 holds the value 7.

=Indirect (C2) retuns the range of values named "Sales", because that's the text inside the cell C2. The same is returned by the formula = Indirect ("Sales") . Here you need the use the double qoutes, since you hardcoce the text value.

 

[Legacy 143009]

Write "West" to D2.

Create a worksheet named "West".

In A1 cell but any value. Let's write 56.

Go back to the previous sheet and in F2 write:

=INDIRECT(D2 & "!A1")

F2 will show 56.

How it works:
"A1" is some string for Excel. It can not recognize as cell referance when it is written in quotation marks. Alsa "West" is a string value stored in D2.

INDIRECT function simply evaluates "West!A1" string as a cell referance. In other words, INDIRECT converts any text into real cell addresses.

But it is not recommended to use INDIRECT often because it is a volatile function. Which means, it doesn't store values. It will recalculate everytime you open the workbook which may increase your file opening times.

 

[RoryA]

for what ever reason the formula needed is =SUM(INDIRECT(D2)) and not =SUM(INDIRECT("D2"))

You only need to use actual quotes when you are typing literal text into a formula directly.

INDIRECT("D2") passes the literal text D2 to the indirect function, so it just returns the value in D2 - i.e. the text "West". (You can't sum that, since it's text) It stops there and does not return a reference to the named range West.

INDIRECT(D2) is a two step operation. First you have an actual reference to D2 which returns the text West. That text is then passed to the INDIRECT function, which then converts it to a reference to the named range West. You can think of it as being equivalent to INDIRECT(INDIRECT("D2")) if that helps?

 

[jdellasala]

I have rarely used INDIRECT, but in looking at the question I saw the problem and started playing with it:

Book1
	A	B
1	1	55
2	2	55
3	3	55
4	4	55
5	5
6	6
7	7
8	8
9	9
10	10
Sheet1

Cell Formulas
Range	Formula
A1:A10	A1	=SEQUENCE(10)
B1	B1	=SUM(INDIRECT("A1:A10"))
B2	B2	=SUM(INDIRECT("A1"):INDIRECT("A10"))
B3	B3	=SUM(INDIRECT("A1#"))
B4	B4	=SUM(INDIRECT("West"))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
West	=Sheet1!$A$1:$A$10	B2

Amazing how flexible the function is!

 

[Arts]

=Indirect (C2) retuns the range of values named "Sales", because that's the text inside the cell C2. The same is returned by the formula = Indirect ("Sales") . Here you need the use the double qoutes, since you hardcoce the text value.

Hi Grah

Appreciate your response, if I could use this as an example.

if you put the word "hello" in cell A1, now in order to attain the word "hello" you would need to use the formula =INDIRECT("A1") with A1 being in ""

Below "=Indirect(C2) returns....because that's the text inside the cell C2"

No quotations are needed here for INDIRECT(C2) yet to retrieve the text hello we need to use "" as this is the text inside that cell - hope that makes sense I can't seem to grasp the consistency in using ""

=Indirect (C2) retuns the range of values named "Sales", because that's the text inside the cell C2

Arts

 

[Arts]

Write "West" to D2.

Create a worksheet named "West".

In A1 cell but any value. Let's write 56.

Go back to the previous sheet and in F2 write:

=INDIRECT(D2 & "!A1")

F2 will show 56.

How it works:
"A1" is some string for Excel. It can not recognize as cell referance when it is written in quotation marks. Alsa "West" is a string value stored in D2.

INDIRECT function simply evaluates "West!A1" string as a cell referance. In other words, INDIRECT converts any text into real cell addresses.

But it is not recommended to use INDIRECT often because it is a volatile function. Which means, it doesn't store values. It will recalculate everytime you open the workbook which may increase your file opening times.

Thanks for this, currently at work and saw multiple replies came in at once I shall go over and hopefully that moment of understanding hits me

 

[Arts]

You only need to use actual quotes when you are typing literal text into a formula directly.

INDIRECT("D2") passes the literal text D2 to the indirect function, so it just returns the value in D2 - i.e. the text "West". (You can't sum that, since it's text) It stops there and does not return a reference to the named range West.

INDIRECT(D2) is a two step operation. First you have an actual reference to D2 which returns the text West. That text is then passed to the INDIRECT function, which then converts it to a reference to the named range West. You can think of it as being equivalent to INDIRECT(INDIRECT("D2")) if that helps?

thanks for this Rory

"You only need to use actual quotes when you are typing literal text into a formula directly." - this quote has shined some light of understanding, I'll take in the rest on my lunch break and hopefully it sinks in!

 

[RoryA]

Another way to put it:

1. INDIRECT expects a text argument passed to it, which needs to be the address or name of a range.
2. In any formula, "D2" is just text, whereas D2 is a direct reference to cell D2, and will return whatever is in D2. So, as a simple example:

Let's say D2 contains the number 4.

="D2" will just return the text "D2"
=D2 will return whatever is in the cell D2 - namely the number 4

=INDIRECT("D2") will return 4, since you passed the text address "D2" to the INDIRECT function, and it then returns whatever is in the cell at that address.
=INDIRECT(D2) will return a #REF error since it evaluates to =INDIRECT(4) which doesn't make sense.

If you select the last formula in a cell and use the formula evaluation tool, you can see the two step process - evaluating D2 then trying to evaluate INDIRECT(4).

 

[Arts]

=INDIRECT(D2) will return a #REF error since it evaluates to =INDIRECT(4) which doesn't make sense.

Right it is this bit that is throwing me.

=Indirect (C2) retuns the range of values named "Sales", because that's the text inside the cell C2. The same is returned by the formula = Indirect ("Sales") . Here you need the use the double qoutes, since you hardcoce the text value.

in the above Grah mentioned INDIRECT(C2) returns "sales" but here there were no quotations used, he has manged to retrieve the text without using them?

where as if had the word "Hello" say in cell D1 I would need to use INDIRECT("D1") to retrieve the text "hello" correct ?

(Sales is a named range is this why) but even the concept of retrieving text seems to differ from my hello example above