Question description:
This user has given permission to use the problem statement for this
blog.
Hello,
I need help regarding indirect formula of excel.
1. What is the purpose of using this formula?
2. If we use this formula with sum formula, then why we are using this with sum formula, we can also perform addition from different sheets without indirect formula ?
Solved by O. D. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
03/09/2018 - 10:57
hello
Excelchat Expert
03/09/2018 - 10:57
Hello, I understand that you need help in understanding the INDIRECT() function, right?
User
03/09/2018 - 10:57
yes
Excelchat Expert
03/09/2018 - 10:57
Okay, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
03/09/2018 - 10:58
The Indirect() function is usually used to allow one to "dynamically" create references.
Excelchat Expert
03/09/2018 - 10:58
By dynamically, I mean, you can concatenate texts to reference different sheets or ranges.
Excelchat Expert
03/09/2018 - 10:59
Easiest way to understand this is by an example.
User
03/09/2018 - 10:59
can you please use easy language, as i am unable to understand the technical terms like "dynamically"
Excelchat Expert
03/09/2018 - 10:59
Sure, by dynamically I meant, it allows you to custom build references.
User
03/09/2018 - 11:00
okay
Excelchat Expert
03/09/2018 - 11:00
So, I'm going to show you an example.
User
03/09/2018 - 11:00
okay
Excelchat Expert
03/09/2018 - 11:00
Do you see the sheet to the right?
User
03/09/2018 - 11:00
yes
Excelchat Expert
03/09/2018 - 11:00
How would you normally SUM the values from A1:A4 in Sheets2,3 and 4?
Excelchat Expert
03/09/2018 - 11:01
You'd write something like this, right?
Excelchat Expert
03/09/2018 - 11:01
=sum(Sheet2!A1:A4)
User
03/09/2018 - 11:01
=sum(A1:A4)
Excelchat Expert
03/09/2018 - 11:01
=sum(Sheet3!A1:A4)
Excelchat Expert
03/09/2018 - 11:01
=sum(Sheet4!A1:A4)
User
03/09/2018 - 11:01
yes
Excelchat Expert
03/09/2018 - 11:01
Yes, that works if you are referencing the same sheet, but if you need to sum in other sheets, you need to reference the sheet names.
User
03/09/2018 - 11:02
yes
Excelchat Expert
03/09/2018 - 11:02
So this will work but if you have plenty of Sheets, then it would be inefficient to write them all one by one.
Excelchat Expert
03/09/2018 - 11:02
So what we can do is to "custom build" the reference.
User
03/09/2018 - 11:02
ok
Excelchat Expert
03/09/2018 - 11:03
So if you'll look at column C, you'll see that I custom built the Sheet names in column A and joined it with A1:A4.
User
03/09/2018 - 11:04
yes
Excelchat Expert
03/09/2018 - 11:04
If you'll look at the formula in column B, you'll see that the formula has those within.
Excelchat Expert
03/09/2018 - 11:05
So basically, we just need to SUM what we have in column C. But, if we do that, we'll get an error because Excel does not recognize the result in column C as a valid reference.
Excelchat Expert
03/09/2018 - 11:05
You'll see that nothing happens in column D when we SUM the result of COLUMN C.
Excelchat Expert
03/09/2018 - 11:06
So in order for Excel to recognize that the result in column C should be a valid reference, we'll enclose it in INDIRECT().
Excelchat Expert
03/09/2018 - 11:06
Now, the final formula will look something like in column E.
Excelchat Expert
03/09/2018 - 11:07
The formula in column E is now "dynamic" or can change without having to alter the actual formula. The formula in column E, relies on what sheet name is written in column A.
Excelchat Expert
03/09/2018 - 11:08
For instance, you can try and change the Sheet name in column A to Sheet5 or Sheet6 and the formula in column E will automatically adjust itself.
User
03/09/2018 - 11:08
its getting confusing, please explain in more easy way, or just in easy language
Excelchat Expert
03/09/2018 - 11:08
Okay, I'll try but I'm not sure how else to explain it in an easy language because I've already broken it down step by step.
Excelchat Expert
03/09/2018 - 11:08
May I know the level of your Excel?
User
03/09/2018 - 11:09
i am mediater
Excelchat Expert
03/09/2018 - 11:09
Do you know how concatenate works?
User
03/09/2018 - 11:09
yes I know
Excelchat Expert
03/09/2018 - 11:10
Do you understand how the column C is created?
User
03/09/2018 - 11:10
i understood till column B, but got confused after that
Excelchat Expert
03/09/2018 - 11:11
Column C is just a concatenate that combines the sheet name in column A and the range "A1:A4"
User
03/09/2018 - 11:11
you have applied formula written in column C in column B, right?
Excelchat Expert
03/09/2018 - 11:11
Okay, the goal here is to recreate what you see inside the SUM() function in column B.
User
03/09/2018 - 11:12
ok
Excelchat Expert
03/09/2018 - 11:12
If you'll notice the value in column C is the same as the text inside the SUM() formula in column B, do you see that?
User
03/09/2018 - 11:12
yes
Excelchat Expert
03/09/2018 - 11:13
So basically, what we are trying to do is using SUM to the value in column C.
Excelchat Expert
03/09/2018 - 11:13
So if you enclose the value in column C in SUM, then you'll get
Excelchat Expert
03/09/2018 - 11:13
=sum(A2&"!A1:A4")
Excelchat Expert
03/09/2018 - 11:13
Or =sum(Sheet2!A1:A4) because A2&"!A1:A4" is equals to Sheet2!A1:A4
Excelchat Expert
03/09/2018 - 11:14
Do you follow so far?
User
03/09/2018 - 11:14
yes
Excelchat Expert
03/09/2018 - 11:14
Okay, the problem with this is that Excel does not understand this because A2&"!A1:A4" is not a valid range.
Excelchat Expert
03/09/2018 - 11:15
That's where INDIRECT() comes in. INDIRECT's only purpose here is to make A2&"!A1:A4" a valid range.
User
03/09/2018 - 11:15
okay, now got it
Excelchat Expert
03/09/2018 - 11:15
So if this is not valid:
Excelchat Expert
03/09/2018 - 11:15
=sum(A2&"!A1:A4")
Excelchat Expert
03/09/2018 - 11:15
=sum(INDIRECT(A2&"!A1:A4"))
Excelchat Expert
03/09/2018 - 11:15
This will make it valid.
User
03/09/2018 - 11:15
got it
Excelchat Expert
03/09/2018 - 11:16
Okay, to the question as to why you'd want to use it this way, simply because you can custom build your formulas this way.
Excelchat Expert
03/09/2018 - 11:16
For instance, you can try changing Sheet2 in A2 to Sheet6.
User
03/09/2018 - 11:16
okay
Excelchat Expert
03/09/2018 - 11:16
You'll see that it will sum the values in Sheet6 instead. Unlike column B which will ALWAYS sum the Sheet2.
User
03/09/2018 - 11:17
alright
Excelchat Expert
03/09/2018 - 11:17
This is what I meant by "dynamic" earlier.
Excelchat Expert
03/09/2018 - 11:17
So that's the advantage of using INDIRECT().
Excelchat Expert
03/09/2018 - 11:17
Would there be anything else that I can help you with regards to the original question?
User
03/09/2018 - 11:18
nothing, thank you so much
Excelchat Expert
03/09/2018 - 11:18
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
03/09/2018 - 11:18
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
03/09/2018 - 11:18
Thank you for contacting Got It Pro. Have an awesome day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
