Hi, in this video, we learn something about indirect formula, a formula which people rarely use. But once they start using it, they 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 of other formulas such as we look up match, index, match match, or some ifs or if statement. But to understand this, we need to undergo some experiments in Excel, which we will allow. So we'll start with experiment number one. First, let's start with the first experiment.
Now, in the yellow cell, I'm writing a five. Now you gradually do why am I writing a five but at this moment, I'm writing a five. And I may ask you that if I write a formula below that, which points to the cell above, and if I press enter, what is going to be the natural answer? If I, I agree, this appears to be a very basic question. But now in contrast, whatever written right now, I am going to write a formula called indirect. Indirect.
I'm pointing to the yellow cell using the brackets. Let me first press enter and show you what comes out what gets displayed as a result, and then we'll discuss about 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, that desk contain an address chip, the address is containing the address which is a five and that's the reason it is taking the value from a file. To understand this through an illustration, you look at this particular diagram, a person who has been asked to go to an address the unless he finds that address chip, he will not be able to locate the address to which he must go to.
He is being directed to a desk, which contains the address CIT, in this case, Five, and using that a five or the cell reference or the address check, he's able to pinpoint the exact location to which he must go to. So basically we are going to relate this example with our experiment. That is, it x formula is asking you to go to a particular desk, which is v3, the cell v3, and the cell v3, which you will consider as a desk contains the address ID a five and address it leads us to the right location, which now contains blue. At this moment, if I write a six, enter black, if I write a one, enter, you get experiment one, and if right a to nothing zero, it's a blank cell. If I write a space one, notice I'm getting a reference error because this is an invalid address, the correct addresses 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 as color one with no spaces.
To the phenomenon of naming a cell or range, if you want to know more about it, please refer to our early videos. And at this moment 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 to and must make sure that I press Enter only and only then the name does get registered. So by now, color one is indicating the cell a five and a color two is indicating black. Now, if I write color one, isn't it a valid address for XL right now, if it is that I can effectively use indirect formula pointing to the cell, this is just the desk on which the address sheet is placed. And using that address of color one, it pinpoints me to the value of blue.
If you want to know more about the names you can also go to Formulas tab and look at Name Manager, because Name Manager will contain some of the name which I have recently given Some names which were given previously. Secondly, the other three names which are not required and just be content with the two for our experiment. This may seem theoretical but believe me after one or two more videos on indirect formula which are there in the series, this will be crystal clear