Original subtitles

In the last lesson of this section, we're going to take a look at the indirect function now of the

indirect function does is it indirectly references another cell to return a result.

So let's take a look at a very basic example of indirect in action.

So I'm going to I'm going to go somewhere down here in the spreadsheets that's select cell I-10, and

I'm going to type in another cell reference.

Let's go for a sake 10 in here now in Cell K 10, maybe I have a number like 300.

So what I could do over here is I could use indirect.

And notice that we have two arguments.

The first argument is the reference text, so I could select this sound just here.

Close the bracket and hit enter.

And the results, I guess, is going to be 300 because I've indirectly referenced this cell over here.

The indirect function is referencing directly cell IE10, but in cell, it said We have a cell reference

to Cell K10 and Cell K10 contains 300, which is why we're getting the result of 300.

So that is indirect in its most basic form.

So again, you might be looking at that and thinking, OK, I understand that, but how is that going

to be useful to me?

Well, let's take a look at our first practical example.

What I have here are a number of sales managers and we have the different regions north, south, east

and west and the amount of sales each of those managers have generated.

And basically, what I'm trying to do here is in Cell H4, I want to pull back the total sales for whatever

region I have in Cell G4.

Now you might be thinking to yourself, Well, can't you just do a sum calculation here?

Well, yes, I could.

I could say equal some.

We're looking for the North figures, so I could select this range just to close the bracket and answer,

and it's going to give me the total sales for the North region.

Notice in the formula that I have the different regions sets up as named ranges.

Now the drawback with this is if I was to change the region, so let's change that to south.

My values don't update because I've got nothing in this formula which is referencing these region names

so I can get around this by using indirect with some.

So we're going to start out with some and we're going to go straight in to indirect.

Now our first argument is the reference text.

So the reference text I'm going to use to do this, some calculation is the region that we have stored

in Cell G for.

Let's close off an indirect function and close off as some.

So if I hit enter now, I'm getting the total sales for the south region.

And if I select this cell range and take a look down in the status bar, I should find that my some

calculation matches what I have in Cell H4, which it does.

Now the advantage of doing it this way because we are indirectly referencing this range of cells via

this cell just here.

If I change the regions, if I change that to North, the figures are all going to update.

So that is one practical example of how you can use the indirect function.

Let's look at something now a little bit more complex now on this spreadsheet, I have a list of different

tools or the countries that these tools go to, and I have how much those tools of generated in sales

from January to July are right at the bottom.

I have a totals row showing me the totals for each of those months.

Now what I'm interested in when I'm looking at this worksheet are basically the total sales for the

month just gone so effectively the current sales.

So the current sales are always going to be this value here is going to be the total for the previous

month.

Now, bear in mind, this data is going to change each month.

So next month we're going to have another column in here which is going to have all of the August figures.

So we need to build our formula so that as we add columns of data in the current sales is always updating,

so always moves across one, across one, across one to grab that total sales figure.

And we can use indirect to help us do this.

So let's click in Cell K.

We're going to say pain equals indirect.

Now, this time we're going to use both of these arguments.

Now, the first argument here is reference text.

Now, one thing you need to understand is when you're working in Excel, there are two different ways

that you can reference cells.

Now, the most common way is to use cell references, so A1 B to C three, so on and so forth.

The other way that you can reference cells is to use what we call our one c one referencing.

And the only difference with this is that our one c one lets you specify the row and the column.

So if we just come out of here for a moment, so you understand what I mean, something that was written

out like R 10, C two, that would basically mean row number 10 column number two.

That is our one c one referencing.

Now for this formula that we're constructing, we need to use that style of referencing.

So let's type in indirect.

There it is.

So the reference that we're going to be using this time is our one C1, one style referencing.

And what do we actually want to reference?

Well, we want to reference the total row that contains the value that we're interested in.

So the total row is row 15.

But the column that we're choosing is going to change depending on how many columns of figures we have

in this table.

And remember, that's going to change each month.

So we need to do something a bit different now when we're using this, our one C1 style, we need to

put this in quote marks.

So the first part of this is fairly straightforward.

We want to reference row number 15.

But when it comes to the column that we want to reference, I don't know the number of the column because

it's going to change each month and we need to allow for that.

So I'm going to add some more quote marks and we're going to use the ampersand to concatenate and then

we're going to go straight into account.

A. We're going to get excel to count the total number of columns in order to find where that last column

is and we're going to count in row.

15.

Now the reason why I'm selecting the whole Rohingya is because this is going to accommodate any new

rows that we add.

Let's close the bracket.

And then the last argument on the end here for this indirect function is the start of referencing that

we're using now we're using our one C1 style in this case, so we need a false argument on the end.

And let's close off our indirect.

Now this is a reasonably complex formula that I'm showing you here.

This might be more suited to the advanced Excel course, but I thought I'd throw this in here just so

you can kind of get an idea as to how you can combine these functions together to get the result you

need.

So let's hit, enter and see what we get.

What we should get is the total for the last month, which is July, and I can see that, yes, we do.

Now I'm just going to apply a little bit of formatting.

So this looks the same.

So the way that we've constructed this formula, if I was to add another column in here, so I'm just

going to copy this across and it's changed this.

So we've got August there and let's change some values so that we have a different totals, let's say

5000 or go ten thousand in here, let's drag the total across.

And would you take a look at that now?

This is updated and it's showing me the new month's total.

So we've introduced a lot of new concepts in that we're just getting a head around how indirect works.

We've also introduced the new all one C1 style of referencing, and we've introduced a little bit of

concatenation and how you can use count to find the last value in the last column and future proof this

formula a little bit for when you add new columns onto the end, this one is definitely one that you

should have a little practice and play around with so that you really understand exactly what you're

doing when you're using this style of formula.

But hopefully that gives you a better idea as to how indirect works and a couple of practical examples

of how you can use it.