Hi, in this video we will learn about indirect formulas. A formula which people rarely use, but once they start using will find immense benefit in almost any formula. And gradually, you will see how powerful this formula is if you start using it in combination with other formulas, such as V lookup match index match, or some F, or if statements. But to understand this, we need to do some experiments in Excel, which we will do now. So we will start with experiment number one. So let's get started.

Now in the yellow cell, I'm writing a five. Now you will soon learn why I'm writing a five but at this moment, I'm entering a five. So if I enter a formula which points to the cell above, and if I press enter, what is going to be the natural answer a five right? But this appears to be a very basic question. So let's take a look at a different formula. I'm going to write a formula called indirect.

So indirect. I'm pointing to the yellow cell, closing the brackets, I'll press enter and show you what comes out what gets displayed as a result, and then we'll discuss it. Notice this time I'm getting the term blue. So basically indirect formula is asking the formula to go to a particular cell, and the cell may act as a desk. And that desk contains an address note, the address happens to be a five and that's the reason it is taking the value from a five. So to understand this, through this illustration, you take this particular diagram, a person who has been asked to go to an address now unless he finds the address note, you will not be able to locate the address, which you must go to.

He's being directed to a desk which contains the address note, in this case a five. And using that a five or the cell reference or the address note, he's able to pinpoint the exact location which he must go. So we're going to relate this example with our experiment. That is an indirect formula is asking you to go to a particular desk, which is B three, the cell B three, which represents the desk contains the address note a five, and the address note leads us to the right location, which now contains blue. Now if I write a six, enter, black, if I write a one, enter, will get experiment one. If I write a to nothing, zero, it's a blank cell.

Write a one notice I'm getting a reference error because this is an invalid address. The correct syntax is a one now as a sub experiment. Let's do one more task. That is I'm going to choose this particular cell and name this Color one with no spaces. This is the phenomenon of naming a cell or range. And if you want to know more about it, please refer to our earlier videos.

And now after writing the name of the cell, I'm going to press enter. Similarly, I go to the next cell and I'm going to give a nickname of color to color two and I must make sure I press Enter only then does the name get registered. So we're now color one is indicating the cell a five and color two is indicating black. Now if I write color one, it is a valid address for Excel right now, if it is then I can effectively use indirect pointer to the cell. This is just the desk on which the address node is placed. And using that address of color one points it points me to the value blue.

Now if you want to know more about the names, you can also go to the Formulas tab and look at Name Manager because Name Manager will contain some of the names which I have recently given And some of the names which were given previously, I can delete the other three names which are not required, I just need to for our experiment. Now this may seem theoretical, but believe me after one or two more videos on indirect formulas, which are there in the series, this will be crystal clear, as a continuation of the previous discussion where we were talking about the basics of an index formula. This time let's do another experiment. Now, you may be wondering where the index formula could actually be applied. This exercise will help answer that question, we have three columns, so January, February, March, and each of them has two numbers placed in a below it.

Now, that's Jan One, two, fib three, four, March five, six. Now we want a mechanism by which if I change the yellow cell, let's say March, it should I should be getting 11. If I write fab, then I should be getting seven Basically the sum of the numbers underneath it. Now one option could be that you start using if people do that, they say, if the is equals February then in that case, it should fetch me a summation of these two values. Perfect. Let's go ahead and ask another question.

If the same cell A 15 is equals to Jan, that n double quotation, then in that case, sum should be based on these two values, else sum should be on the march closing the brackets once once again, and once again, and that's the formula. Now, notice just for three months, the formula length is so large, imagine you have 12 months data, the length of the formula would be have four times this. Now so we want to faster and easier mechanism which gives us the answer in a very small An easy formula. And the main point is it should increase in length, the moment more months come into the picture. Now let's see how to achieve this. First I'm going to choose these one into a name.

This is Jan. You can use uppercase lowercase in writing the name Jan, but make sure that after writing the name, you press enter. If you have these two cells, fab press enter these two cells I'm writing March and pressing Enter. So here I have three more names, fab pointing to these two cells, Jan pointing to these two in March pointing to these two cells. So now what if I write a sum formula and this sum needs a location from where the numbers must be picked up for totaling up now the location is dependent on the yellow cell. Basically this the address on the note If the address changes to some form of that will go to a different address, but who will lead me to this address the concept of redirection that is going to be given by indirect formula. So indirect go to this particular cell that contains the address and this will tell you where to go.

So, as of now, fib is pointing to were three and four. And because of which I'm getting seven, by the way, if you wanted to know what is going on, in excels mind, we can simply choose the indirect formula. Right? Starting from I with a bracket of in direct now pressing the function key f9. So the moment I change this, Jan one, I get three. So the moment I changes to March, I'm getting five and six.

So internally what is happening indirect formula is a formula which is going to this test is taking the address and going to the right location. And these two numbers once it is fed into the sum formula, it quickly adds up these numbers. Now you might ask if There are nine more months of data. Do I need to name these two cells respectively? For all the remaining nine months? The answer is no.

So let's see how fast this could be done. Let's say I have data through December. And what I'm doing is I'm simply going to add one to it, the previous cell, so that we populate a random set of numbers for our exercise. Now instead of naming them individually as April, May, June or July, I can just choose the entire block including the name, as discussed in one of their previous videos with respect to names mass naming or bulk naming. We have a technique we can use, we go to Formulas tab, and there's a button called create from selection primarily it points to the fact that you can create the names from the selected area. So I click on Create from selection and it says Create names from selection.

I take the names from the top, and if I press OK, it's seems that nothing has happened on my screen. But to know more, let's look at the drop down of the name box, I'm getting all the names of the months in a three letter format, which have been given the heading. Just for testing. If I put let's say July 2.2, these two cells and to make the formula work, I simply modify the drop down list such that it incorporates the other nine months. So I go to the yellow cell data tab within which we look for data validation and click on Data Validation. The items suggest allow a list but the source is restricted to only these three cells.

So let me change these selections right from Jan till December, and as I press OK, this drop down has all the 12 months values. So if I now choose August, let me check 10 1121. So there you go with the same length of that formula, we are able to derive an answer from Jan through December. And this is Using the technique of indirect and naming in which we use it in a much more powerful manner in the next few videos. Till then, practice this exercise. See you in the next video.

Thank you