(playful cartoon music) Let's take a look at Excel's INDIRECT function. Now INDIRECT is a bit
of a strange function and it takes a little bit
of time to get the hang of. But it's very useful because
it can easily handle cases where you have different ranges that you need to feed other formulas. Let me give you an example. Here I want to get the total revenue depending on the year that I select. Just have two years in this example of the divisions that I have listed here. Now the tricky part is, my data is not set up in one single data table and one single tab. It's actually split
between two different tabs. So this tab is for 2016 data only and this tab is for 2017 data only. You can see I formatted my
table as an official Excel table and this one is called
data_py for previous year and the other one is called data_current. So what I want to do is
to get the full revenue depending on this. It's a condition, right? The formula I would be using is the SUMIF or the SUMIFS function, but I need to look in different tabs depending on the year that's chosen here. INDIRECT is great for this because it can easily
refer to different ranges. So, if you only were doing this and it's only these two options you could use separate
IF statements, right? So you could say IF this is 2016 THEN you can write a whole SUMIFS that references this tab only and IF this is THEN 2017
you can write a whole SUMIFS that references this tab only. What INDIRECT does is that
you write the SUMIFS once and then you use INDIRECT
to reference these things. So it becomes very simple
if you have a lot of tabs, you don't need to manage
different formulas. Before we get here let me
show you what INDIRECT does. In terms of the arguments
it needs it's very simple. It just needs one mandatory argument and that's referencing a cell or putting in text. So I'm going to reference this cell. What do you think I'm going to get? Hello there... Why? Why doesn't it give me I6? What is I6 actually? What is in cell I6? It says Hello there... So what INDIRECT is doing, it's actually indirectly going to cell I6. Basically it uses the
cell that you give it in the formula as the messenger to find out where to go. So cell A6 is the messenger and it's telling it "Go to I6". So that was the second optional argument that you see here. It says A1. It's true if it's A1 style. So, like the style I have set up or R1C1 where you're
referencing two row numbers and column numbers. And by default, it's true. What happens if I don't put anything here? Error. The messenger has nothing to say. It doesn't know where to go. What if I put something like hello? Error. It's trying to look for an address that's called hello. There is no address, it's giving an error. So what about in this case? Do you think I'm going
to get an error here? I get hello there... Why? Why does it give me an error for hello, but it gives me a value back for greeting? Because greeting is in fact an address. And it's the address of this cell. Notice here I've named this cell greeting. So, it sees it as text, but it translates it first to an address. And because it exists, and you can see it also
here in name manager that I've named this cell I6 greeting because it exists, it goes and finds the value
that's in the final destination. Okay, just to see that this is dynamic, just going to put an
exclamation mark there and you can see it here. If I were to call A1, I can directly type it here, hello. Now this returns INDIRECT function. Before we move on to our example let me just show you one more thing because we saw the cell
referencing part of this. Let me show you what happens if I put this in quotation marks. So I'm basically turning A6 to text. I get hello. So now, it's returning
what is in this cell. Because what INDIRECT is doing is it's translating
text to a cell reference and then it's giving me
what is in that cell. Notice the difference I take
away the quotation marks, I go to the address that's hello, it's acting like the messenger, I put the quotation marks, it translates this text
to a cell reference, and it goes here and returns the value. So the more advanced you get in Excel, the more uses your going
to find for the different ways to use INDIRECT. So now moving onto our example here. The way I go about this one is I start off very simple. I act as if I don't have the split here. I create my formula as if everything is going to one single tab. And then I try to see how I
can use the INDIRECT function in that main formula because
doing it all at the same time can be very confusing. Because I want to get total revenue, so let's assume only for 2016, I'm going to use the SUMIFS function. So first thing is the SUM range. It's this one. And good it's referencing
to the table headers. That's exactly what I want. Criteria_range1 is my division, and the actual criteria
is sitting on the report and it's this one. Okay, I'm referencing table headers. I don't need to fix anything. I can just pull it down. So that looks right. Let's just double check these numbers. That's 187. 187. This is until now my dynamic, right? So our aim is to make this part dynamic. So when this is 2017 it
actually looks in the other tab. What I've done here is to put the years that I have in the drop tab and to put the tables
that it should look into. This is what I don't have in common between these two tabs, right? The rest, the header of both tabs they're identical, right? This is revenue and division as well. So, this is part of my cell reference. It's not the entire thing. I also need to combine it with revenue. What I can do is do a vlookup here depending on the lookup value here I'm going to look in this table and return the table name. and I want an exact match. And I still need the revenue headers. So I'm going to combine
this with my header Revenue. That's exactly how it should look. Now I'm just going to copy this, press escape, go here, paste and replace Revenue with Division. When I switch this to 2017 this changes. Okay, so the tables are changing, so my address is changing. So now what I need to do
is replace this referencing or this direct referencing
with an indirect referencing of this one, because I'm summing revenue first. That's the first part. And then for Division I need to reference this cell. I want to pull this down. I just have to be careful
that I do need to fix this. Now this should be referencing 2017. So let's just check that
Utility 2017 is 189. 189. And in 2016 it's 187. That looks good. So my range references are changing depending on the selection. That's one way that you can
use the INDIRECT function. Another way you can write this if you want to avoid
these helper cells here you can do the vlookup directly
inside the indirect formula. So we're looking this up in here. We want the second column back false because we want an exact match and we're going to
combine this with Revenue, but I actually need the quotation marks because I'm combining text in there and close brackets for INDIRECT. And now I'm going to use the same part and replace this one and combine the Division. Okay so, before I copy this down let's just make sure that
I have fixed everything. This needs to be fixed as well. This needs to be fixed. And this needs to be fixed. Let's check. For 2016 that was the 187. And that's the 189. So this is another way
that you can use to write the INDIRECT function. Now one thing you need to watch out for is that INDIRECT is a volatile formula. And it can really slow things down if you have larger spreadhseets and you're using INDIRECT function a lot. So you're feeding it to a lot of formulas. You're going to see an
impact on performance. So keep that in mind when you're using it in larger spreadsheets. (playful cartoon music)
