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.
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
If I understand the query below correctly, the one idea is to type some words in a cell, and then, while holding the ALT key down, click ENTER. Within the cell you will get another line. Hope this helps. Original query Hi, … Continue reading
A recent email query asked how to share and unshare workbooks in Microsoft Excel with a specific focus on switching off the sharing. The method to share and ‘unshare’ the workbook is the same. As shown in the image below, … Continue reading
“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”– Absa Capital