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

 

[RoryA]

If you put literal text in a formula directly, you have to enclose it in quotes. Otherwise Excel will assume it is either a function or a name. If you return text from a cell using a cell reference, it does not have to be in quotes because the calculation engine already knows it is text.

The C2 in INDIRECT(C2) is a direct cell reference and will be evaluated before anything else happens. Whatever is in C2 will then be passed to the INDIRECT function for it to evaluate as a range reference.
The "C2" in INDIRECT("C2") is simply text and so that formula will return whatever is in C2. Hence, =C2 and =INDIRECT("C2") are calculated exactly the same way.

It's no different if you are using the name of a range rather than a simple address. If you put it directly in the formula as literal text, it has to be in quotes:

=INDIRECT("Sales")

whereas if you have the word Sales in C2, you can use:

=INDIRECT(C2)

and both will return a reference to the range named Sales, which you can use in other functions like SUM.

If you used =INDIRECT(Sales), then Excel will evaluate the contents of the range named Sales and pass the result(s) to the INDIRECT function, which may or may not return an error depending on what is in the range.

 

[Arts]

The "C2" in INDIRECT("C2") is simply text and so that formula will return whatever is in C2. Hence, =C2 and =INDIRECT("C2") are calculated exactly the same way.

The above makes sense but then falls apart for me in the next reply below. The below now loses the quotations above has INDIRECT("C2") below is now saying INDIRECT(C2).

If I have the word "sales" in C2 the above and below are different formulas in order to return the word "sales" - I hope this explains it best how I am seeing it hence the confusion.

If I have the word "hello" in D2 I would use INDIRECT("D2") and this would return hello but here you have said if I have the word "sales" in C2 it would be INDIRECT(C2) which in affect is returning "sales" but seems to be inconsistent. I hope that is making sense in the how I am seeing it and with it not being consistent, both return the words but are constructed different i.e ""

I think I'm going to have to wave the white flag on this on and not irk you in the process for another response, for what ever reason I'm just not seeing it/understanding it.

I'll try and break down how I am seeing it in my head and how it's being broken down by my thought process....

If I have the word "hello" in cell A1 in order for me to retrieve the contents of that cell I would need to use INDIRECT("A1") which returns "hello". - Correct? As INDIRECT(A1) would returns #REF
so by using this logic with your example below

If I put the word Sales in C2 and wanted to retrieve this I would need to use INDIRECT("C2") - Correct ?

in your example below we have =INDIRECT(C2) which would give me "sales"

but if I wrote sales in a cell C2 and used INDIRECT with this I would need to use INDIRECT("C2") to get the result "sales"

It's no different if you are using the name of a range rather than a simple address. If you put it directly in the formula as literal text, it has to be in quotes:

=INDIRECT("Sales")

whereas if you have the word Sales in C2, you can use:

=INDIRECT(C2)

I thank you for the time taken taken in responding to me, should you have the energy to read the above step by step showing where I am confused and post back that would be great.

 

[RoryA]

If I have the word "sales" in C2 the above and below are different formulas in order to return the word "sales"

No. The first two =C2 and INDIRECT("C2") will return a reference to the cell C2, which contains the word Sales so the result will just be the word Sales.

INDIRECT(C2) returns a reference to the range called Sales. It is equivalent to INDIRECT("Sales"). C2 is evaluated as a direct reference first (returning the word Sales just as in the first formula above) and that word is then passed to INDIRECT to return a reference to the range Sales. There is one additional step compared to the first two formulas. You could then use that in say a SUM formula to add up all the sales.

The two blue highlighted parts above are the same reference. So, since C2 and INDIRECT("C2" evaluate the same way, INDIRECT(C2) is actually equivalent to INDIRECT(INDIRECT("C2")) - i.e. there is one more level of indirection. We get the contents of C2 first and then pass that result to the outer INDIRECT function.

 

[RoryA]

In case it helps to clarify, let's say that the range called Sales refers to A1:A10.

=SUM(A1:A10) and =SUM(Sales) are the same. In the latter formula, Sales is evaluated as a range referring to A1:A10 first, then the SUM function adds up that range.

=SUM("Sales") would just be trying to sum a bit of text, so returns 0.

Now let's go back to INDIRECT. Let's say C2 contains the word Sales again.

As mentioned above, C2 or INDIRECT("C2") will return the word Sales, not a reference to the range called Sales. So either of these:

=SUM(C2)

or

=SUM(INDIRECT("C2"))

are equivalent to the earlier:

=SUM("Sales")

and do not work. What we need is:

=SUM(INDIRECT(C2))

since that evaluates step by step (you can check this in the formula evaluation window):

Step 1:

Step 2:

Step 3:

 

[Arts]

No. The first two =C2 and INDIRECT("C2") will return a reference to the cell C2, which contains the word Sales so the result will just be the word Sales.

INDIRECT(C2) returns a reference to the range called Sales. It is equivalent to INDIRECT("Sales"). C2 is evaluated as a direct reference first (returning the word Sales just as in the first formula above) and that word is then passed to INDIRECT to return a reference to the range Sales. There is one additional step compared to the first two formulas. You could then use that in say a SUM formula to add up all the sales.

The two blue highlighted parts above are the same reference. So, since C2 and INDIRECT("C2" evaluate the same way, INDIRECT(C2) is actually equivalent to INDIRECT(INDIRECT("C2")) - i.e. there is one more level of indirection. We get the contents of C2 first and then pass that result to the outer INDIRECT function.

so would I ever use INDIRECT with quotations around the cell ie INDIRECT("C2") or INDIRECT("E14") ?

 

[RoryA]

Only if you want a reference to that cell that would never change (including if you delete cells/rows/columns), or you need to create the cell reference by concatenating text (eg if you have a sheet name in one cell, a column letter in another and a row number in another, you can concatenate them together as text that looks like the address of a cell, then pass that to INDIRECT).

Otherwise you would just use =C2 or =E14 for example.

 

[Arts]

Only if you want a reference to that cell that would never change (including if you delete cells/rows/columns), or you need to create the cell reference by concatenating text (eg if you have a sheet name in one cell, a column letter in another and a row number in another, you can concatenate them together as text that looks like the address of a cell, then pass that to INDIRECT).

Otherwise you would just use =C2 or =E14 for example.

I think we'll stop there, there are certain things in life which people just get and other things where things just don't add up and this is one of those for me.

I can do a rubiks cube in under a min, play chess to a very high level but this has got me beat!!

Maybe I need to go back to basics and understand INDIRECT as a whole. I think the problem started with every video I watched seems to use the example of INDIRECT to extract the contents of a cell using "" around it so if you had the number 3 in cell D3 and used INDIRECT(D3) this returns a ref error so you need to use INDIRECT("D3") but am now more so confused.

We'll leave this here, not sure if you have ever watched Big bang Theory but this is seriously turning into a similar episode where Sheldon tries to teach Penny physics...

this is the video that broke me (I think)

(1.11 and then 4.05)

I genuinely thank you for all of your help on this and the patience shown towards me.

Arts

 

[RoryA]

not sure if you have ever watched Big bang Theory but this is seriously turning into a similar episode where Sheldon tries to teach Penny physics...

I remember that one, so fair enough.

 

[Arts]

I remember that one, so fair enough.

I'll absorb what I can so far and may return to you !!

 

[Arts]

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).

After hours of going through this I finally had a bit of eureka moment with this in that the INDIRECT explanation you gave at the bottom sunk through!, I understood why INDIRECT("D2") will return 4. (as this gives us D2 which is the cell reference it needs to extract its value, at least that makes sense in my head)

Just one more thing if I have 4 in D2 as per the example we used/below, and I then gave D2 a named range of hello.

If I have hello in F2 and use the formula INDIRECT(F2) this returns 4 which is fine and what I would expect.

Though if I have F2 in cell H2 as per below and I then do INDIRECT(H2) why is it this returns the text hello and not 4 as I was expecting, the named range has been returned, I would have assumed this would have picked up hello and given 4.