Home // Video Tutorials // 6 - 10 (Advanced) // 6 // Reference Excel Sheet Name

Reference Excel Sheet Name

A function that allows you to reference excel sheet name in a formula so that it looks for that name sheet.

Reference Excel Sheet Name

The indirect function allows you to reference Excel sheet names within a particular cell based on the contents of another cell. You can even specify which sheet should be used in a formula.

The easiest way to explain it, is just to give you an example. So what I have got here is a couple of lookup references and some data scattered all over the place. I am going to create a formula here. so if I did this – if I said equals to that cell, you’ll see it brings through whatever is in that cell- B6. However the indirect formula has the effect – I am just going to go to the Function Wizard and find the indirect formula. If I point here, what it is basically doing, is telling Excel – go, look in this cell- whatever you find in there, that is where you must actually go and look. So can you see it says B6 – what it is going to do is go look in this cell. So when I say OK- can you see it brings back 1.333 – so this cell over here.

Just doing that again – I am going to use the indirect function. I am going to say please look in that cell there and what you’ll see is that it brings back 45, why? because it goes there looks in the cell, says I need to go to D7, finds D7 – gives me the answer here. If for example, I change this to B6 like the one above, the answer now looks there. I’ll just change that back.

It even works for name ranges – so if you look at this cell here, it is no longer called E8, it is called units. I’ve type the word units in here. What I can do is go in here, find my indirect and I am going to say – look at that cell – it knows to look for something called units and when I click Ok -can you see it brings through a 10 – its looking over here.

You can even use other ways to actually create the address – so in this case – this cell here has got a 9 in it – because what I want to do is to use C and whatever number is in there – so the column I am going to give but the row will come from the cell. Again I am going to use the indirect and the reference I am actually going in inverted commas type the “C” and I’ll put the and I’m going to put the amphasand and I’ll point at that cell. So what you can see is Excel is seeing C9 so it’s going to go, find what number is in there – that knows its now C9 – goes to C9 – and brings through that answer. I am going to say OK – and you can see it brings through the 62.

Now let’s show you how you can go across different sheets. So for the first part over here – I’m just going to go look for whatever is in K20 – so I’m going to do our normal indirect – point there – and when I say OK, you’ll see it has gone to K20, which is over here and you’ll see it has got a little sentence in it – this is looking within the sheet. Now we want to look for that cell, but not in this sheet, but in another sheet.

You can build the address up manually by putting all the correct letters and words in, but we like to use the address function – go to the address function – you’ll see it allows you to build up the address, step by step -so row number – ask for row 20 -column is column K which is the eleventh column – you’ll can already see it has already made part of the address. You can decide whether you want it to be absolute or not – doesn’t really matter in this case – and whether you want to use A1 or R1 C1 -style – doesn’t matter again. For the sheet text – if you want – you can just write it out – just not forgetting your inverted commas. And what you’ll see is it takes the combination of this and creates an address. For now I am just going to say OK. And now over here – I am going to use the indirect function and just point it here – when I say Ok – you’ll see that what it is saying is the same as what is over here. So you can basically use the indirect function to go to any sheet here.