How to make an indirect cell reference such that a formula will look at the reference as a direction to where it should actually look.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Indirect Cell Reference in Excel
The indirect function in excel allows you to point excel to a particular cell based on the contents of another cell. Now the easiest way to explain this is to use some examples.
In this example we have some cells here and you will see that this one says B6 and in cell B6 is a number. What we want to do is get a formula that looks at the contents of this cell, sees what it is referring to, and then goes to that cell and picks it up.
The function that allows you to do this is called Indirect. So if I activate the function wizard, and I find Indirect, and say OK. What you see it says is tell me what cell contains the reference to the cell we want. In this case it is over here. You can put in a true or false to say how you like to refer to your cells. Are you using A1 cell or R1C1 style. When I say OK, what you will see Excel has done, is it has gone, looked in this cell, said OK this cell says B6, I am now going to go look in B6 and pull through that number.
So for example if I went to that cell and changed it to B7, and say OK. this formula here says look here, it looks at B7, goes to the cell B7, looks in there and says that’s the number, 45.
Let us change this back to B6 . As already shown we can do the same with B7 and in this case if we just copy it down, you will see the Indirect saying, look at this cell, it is saying B7, goto B7 and pull through the number.
You may be aware that it is possible to give cells actual names. If I click in this cell it is B8, and over here it is called B8. I can change this name to George for example and click enter. So when I click on another cell it is C8 as shown by the references and when I click back here George is shown. You can used these names in your Indirect function as well.
So for example I come and click here, activate the function wizard, find Indirect, say OK. My reference text is sitting here, and because I have put the name George in here, when i say OK, what it does is goes here and sees George, it looks for a named cell called George, finds the cell over here and pulls through the 10.
You can also use the Indirect feature to actually generate a reference. So over here we have 9, which is the row. What we are going to do is create an Indirect function, and for the reference text, what we are going to do is say the reference text is to refer to b, and by using the & sign whatever else is in this cell. You will see that the function wizard tells you that the result of that formula is B9, and when I say OK, the Indirect functions takes that mini formula, turns it into B9, goes to B9 and pulls through the number sitting in that cell