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

 

[itsrich]

OK RoyA, Et Al, Let's take the INDIRECT ' and " placement... I had help developing this over in Indirect Syntax - Outside the workbook
I have crawled all over the internet, All I find is the Microsoft Answwers "Technically correct, but totally USELESS."

Until, I I found this post. The only discussion on why " are placed where they are and the results! =INDIRECT("H2") is the equivalent of =H2. Really, the discussion is a mind bender.

I am looking at F7. This formula works, The #REF! is there because I pulled it out and broke it for discussion purposes.

I want to develop Rules for " and ' in INDIRECT...

INDIRECT("'["&D6&"]"&E6&"'!$D:$D")

INDIRECT("'["&D6&"]"&E6&"'!$D:$D")

Why is the ' where it is? How do I develop a rule for placing it. (highlighted, colored, and bolded to point out the ' ) Also the placement & and "
Why the order? It is used in two place.

• F5 formula is what I wanted to replace (15 work books, 160 tabs, Yes INDIRECT do not work when the files are closed that is because it is a volatile function, but it is the easiest way to develop one formula.)
• F6 formula is the final formula I used in the spreadsheet, the formula give the correct info. It returns "No Source for PRICE" because it is referencing a file that is not open.
• F7 formula is the INDIRECT for the purpose of this question, pulled out of F6

1. a complete side question... Now that I have gotten to a formula that is serviceable (yet volatile). Is there a non-volatile formula that will do the same as F6 without retyping it in every cell? I now open the workbook the F6 is looking for. The volatile formula INDIRECT updates. I copy and paste the updated fields, close the file. Then rinse and repeat 15 more times.

I like the flexibility of INDIRECT, because I can build formulas using the content of cells. But I want the persistence of XLookUp. What I have in F6 is a serviceable work around for now.

Cell Formulas
Range	Formula
F5	F5	=XLOOKUP(B5,'S:\2025 Purchasing\2.SPREADSHEETS\[Cycle Count 1 (Floor) 2025.xlsm]ADORN'!$D:$D,'S:\2025 Purchasing\2.SPREADSHEETS\[Cycle Count 1 (Floor) 2025.xlsm]ADORN'!$F:$F)
F6	F6	=IF(ISERROR(XLOOKUP(B6,INDIRECT("'["&D6&"]"&E6&"'!$D:$D"),INDIRECT("'["&D6&"]"&E6&"'!$F:$F"),CONCAT("No price on ",E6))), "No Source for PRICE",XLOOKUP(B6,INDIRECT("'["&D6&"]"&E6&"'!$D:$D"),INDIRECT("'["&D6&"]"&E6&"'!$F:$F"),CONCAT("No price on ",E6)))
F7	F7	=INDIRECT("'["&D6&"]"&E6&"'!$D:$D")

Master - rework 2024.09.10.xlsx
	A	B	C	D	E	F
2	FR #	Vendor PN	File Folder	File	Tab	Vendor ID Cycle Count 1 (floor) 2025
3	F100309670	DT-SUPPORT	Unkown	Unkown	Unkown
4	F100270595	540G2	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	360 Products
5	F100503650	45210765	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025.xlsx	ADORN	23.21
6	F100405034	45210772	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	ADORN	No Source for PRICE
7	F100381331	2259-180	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	BOYD INDIANA	#REF!
8	F100240548	2259-96	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	BOYD INDIANA
9	F100066852	3840RBK	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	D & W
10	F100424556	3940BK	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	D & W
11	F100349255	3U115	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	H.B FULLER
12	F100318239	3U316	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	H.B FULLER
13	F100349578	AERO 5324P	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	HAHN SYSTEMS, LLC
14	F100358023	AEROSMITH 5323HPST	S:\2025 Purchasing\2.SPREADSHEETS	Cycle Count 1 (Floor) 2025	HAHN SYSTEMS, LLC
Sheet4 (2)