# Indirect Explanation



## Arts (Dec 29, 2022)

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 (Dec 29, 2022)

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



Book1ABCDEFGHIJ12B5SalesSales is a named range3457465767447875598661097711108812991310101415Sheet1Cell FormulasRangeFormulaE7,F7:F13E7=INDIRECT(B2)G7:G13G7=INDIRECT("Sales")H7H7=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.


----------



## Flashbond (Dec 29, 2022)

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 (Dec 29, 2022)

Arts said:


> 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 (Dec 29, 2022)

I have rarely used INDIRECT, but in looking at the question I saw the problem and started playing with it:
Book1AB115522553355445555667788991010Sheet1Cell FormulasRangeFormulaA1:A10A1=SEQUENCE(10)B1B1=SUM(INDIRECT("A1:A10"))B2B2=SUM(INDIRECT("A1"):INDIRECT("A10"))B3B3=SUM(INDIRECT("A1#"))B4B4=SUM(INDIRECT("West"))Dynamic array formulas.Named RangesNameRefers ToCellsWest=Sheet1!$A$1:$A$10B2
Amazing how flexible the function is!


----------



## Arts (Dec 29, 2022)

GraH said:


> =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 (Dec 29, 2022)

Flashbond said:


> Write "West" to D2.
> 
> Create a worksheet named "West".
> 
> ...


 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 (Dec 29, 2022)

RoryA said:


> 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 (Dec 29, 2022)

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 (Dec 29, 2022)

RoryA said:


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



GraH said:


> =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


----------



## Arts (Dec 29, 2022)

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 (Dec 29, 2022)

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 (Dec 29, 2022)

RoryA said:


> 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"



RoryA said:


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




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 (Dec 29, 2022)

Arts said:


> 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 (Dec 29, 2022)

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 (Dec 29, 2022)

RoryA said:


> 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 (Dec 29, 2022)

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 (Dec 29, 2022)

RoryA said:


> 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 (Dec 29, 2022)

Arts said:


> 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 (Dec 29, 2022)

RoryA said:


> I remember that one, so fair enough.



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


----------



## Arts (Dec 30, 2022)

RoryA said:


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



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.


----------



## Arts (Dec 29, 2022)

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 (Dec 30, 2022)

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 (Dec 30, 2022)

RoryA said:


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



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



RoryA said:


> 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 (Dec 30, 2022)

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 (Dec 30, 2022)

RoryA said:


> 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 (Dec 30, 2022)

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 (Dec 30, 2022)

RoryA said:


> You've already used the INDIRECT function though. You had:
> 
> INDIRECT(H2)
> and H2 contains  the text "F2" so that is equivalent to:
> ...



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 (Dec 30, 2022)

Arts said:


> 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 (Dec 30, 2022)

RoryA said:


> 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 (Dec 30, 2022)

Arts said:


> 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 (Dec 30, 2022)

RoryA said:


> 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!!


----------



## Arts (Dec 29, 2022)

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 (Dec 30, 2022)

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 (Dec 30, 2022)

RoryA said:


> Happy to help.
> 
> Here's (yet) another way to look at it:
> 
> ...



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.




RoryA said:


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



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 (Dec 30, 2022)

Glad to be of some help. 

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


----------



## Arts (Dec 30, 2022)

RoryA said:


> 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 😂


----------

