# 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.