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]

Happy to help.

Here's (yet) another way to look at it:

=INDIRECT("H2") is the equivalent of =H2.

If I put F2 in H2 and enter =H2 somewhere, you (hopefully) wouldn't be surprised that it returns the text F2, not the contents of cell F2.

 

[Arts]

Happy to help.

Here's (yet) another way to look at it:

=INDIRECT("H2") is the equivalent of =H2.

If I put F2 in H2 and enter =H2 somewhere, you (hopefully) wouldn't be surprised that it returns the text F2, not the contents of cell F2.

yeah that makes sense

How I was seeing it in my head was when you use the INDIRECT it need not be on the source cell (by this I mean F2 contains hello the "named range" for 4 where it is located in D2).

Now H2 may contain F2, in my head I was thinking where INDIRECT has returned "hello" this is the same as referencing hello from its original cell of F2 as hello is now showing as a value.

There was a bit way earlier (post 11 below) where you mentioned about calculation engine , the calculation engine already knows it is text referring to a named range and I just kinda ran with that to an extent, used INDIRECT(H2) this returned "hello" and it would have known this was relating to a named range.

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.

progress has been made, I shall leave this thread/you alone for a while now, with out wearing it thin, thank you once again for all of your help on this.

 

[RoryA]

Glad to be of some help.

On the bright side, INDIRECT should generally be avoided anyway as it's a volatile function...

 

[Arts]

Glad to be of some help.

On the bright side, INDIRECT should generally be avoided anyway as it's a volatile function...

Well that was time well spent then