# Excel - IF Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.