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]

You have one more level of indirection required.
H2 = the text "F2"
INDIRECT(H2) is therefore equivalent to INDIRECT("F2") - not INDIRECT(F2) - which returns the contents of F2, which is the word "hello". If you want a reference to the range named hello, you need another INDIRECT:

=INDIRECT(INDIRECT(H2))

Does that make sense?

 

[Arts]

You have one more level of indirection required.
H2 = the text "F2"
INDIRECT(H2) is therefore equivalent to INDIRECT("F2") - not INDIRECT(F2) - which returns the contents of F2, which is the word "hello". If you want a reference to the range named hello, you need another INDIRECT:

=INDIRECT(INDIRECT(H2))

Does that make sense?

but isn't it the text we're after as mentioned earlier which in this case is the name of the range?

1. INDIRECT expects a text argument passed to it, which needs to be the address or name of a range.

also what "returns the contents of F2, which is the word "hello"." -
INDIRECT("F2") or INDIRECT(F2)

 

[RoryA]

If it's the text you are after, that's what you got - the word "hello". However, that is the result of your INDIRECT function, not an argument being passed to it. If you want to return the content of the 'hello' range, you need another INDIRECT function.

 

[Arts]

If it's the text you are after, that's what you got - the word "hello". However, that is the result of your INDIRECT function, not an argument being passed to it. If you want to return the content of the 'hello' range, you need another INDIRECT function.

Isn't the text what the INDIRECT needs which in this case is the name of the range "hello"

 

[RoryA]

You've already used the INDIRECT function though. You had:

INDIRECT(H2)
and H2 contains the text "F2" so that is equivalent to:

=INDIRECT("F2")

Once it has returned the content of F2 that's it. The fact that the returned value is the name/address of a range doesn't mean that INDIRECT calls itself again.

 

[Arts]

You've already used the INDIRECT function though. You had:

INDIRECT(H2)
and H2 contains the text "F2" so that is equivalent to:

=INDIRECT("F2")

Once it has returned the content of F2 that's it. The fact that the returned value is the name/address of a range doesn't mean that INDIRECT calls itself again.

I'll take this away and swot away yet again, I should finish work slightly earlier today if not there goes my Saturday morning. Seriously thought I had this!! In the grand scheme of Excel this probably isn't even ranked that high of a thing to get!!

INDIRECT(F2) which is looking at hello brings back 4
INDIRECT(H2) which has F2 in it which is a reference to hello, brings back hello and not 4...mind blown.....

One step forward and two steps back comes to mind

If I don't speak to you again have a happy new years eve and as always thank you for your responses.

 

[RoryA]

INDIRECT(F2) which is looking at hello brings back 4
INDIRECT(H2) which has F2 in it which is a reference to hello, brings back hello and not 4

If H2 has F2 in it, then INDIRECT(H2) is equivalent to INDIRECT("F2") and not INDIRECT(F2), which is why it stops one stage short. You've added another step in the chain in the second formula, so you would need another INDIRECT function.

Have you tried using the Evaluate Formula dialog? It can really help you to see what's happening.

 

[Arts]

If H2 has F2 in it, then INDIRECT(H2) is equivalent to INDIRECT("F2") and not INDIRECT(F2), which is why it stops one stage short. You've added another step in the chain in the second formula, so you would need another INDIRECT function.

Have you tried using the Evaluate Formula dialog? It can really help you to see what's happening.

Yeah I'll give that a whirl, I think I'm getting lost in the concept of it all, where it states "the INDIRECT returns a valid cell reference from a given text string",

I'm thinking I've got the text string "hello" from H2 which is referencing F2 where "hello" is located. It is bringing back "hello" a text string but not computing to the result which I think it should be once it's brought back the text string.

I'll step away from it and come back to it "can't see the trees for the woods" kinda thing going on right now I think (I hope)

I think you'd make a great driving instructor!! you have the patience of a saint!!

 

[RoryA]

from H2 which is referencing F2 where "hello" is located

That's where you've gone wrong I think. H2 just contains the text F2. It is not a direct reference to the cell F2 (if it were, if you inserted a row above row 2, the F2 would change to F3). The INDIRECT then converts that text to a reference to the cell F2, and returns its contents.

 

[Arts]

That's where you've gone wrong I think. H2 just contains the text F2. It is not a direct reference to the cell F2 (if it were, if you inserted a row above row 2, the F2 would change to F3). The INDIRECT then converts that text to a reference to the cell F2, and returns its contents.

This has shed a bit of light on how I am seeing it/interpreting it!

You're right, I'm thinking H2 where it has F2 in the cell is a direct reference to F2. If I put =F2 in H2 and then do INDIRECT(H2) this brings back 4

I'm almost tempted to mark that as a solution!!! I'll go over it a few more times before I do ha

As always thanks for coming back with a reply!!