Sheet Name

Question

If I have sheets Branch 1, Branch 2, etc. with the last sheet name Branch Total and if I am in a cell in Branch Total and want to reference the name of sheet 1 (Branch 1) what do I do.

Answer 1

This formula will give you the name of the sheet (Branch 1) if you wanted to actually show this on the sheet or use it as text in an INDIRECT function or similar (to ensure the formulas keep working if the target sheet name were to be changed), but the answer to your question really depends what you want to do with it:

=MID(CELL("filename",'Branch 1'!A1),FIND("]",CELL("filename",'Branch 1'!A1),1)+1,31)

Cheers,

Dave

Answer 2

One easy way to deal with this is to let Excel do the work for you:

Go to the cell in [Branch Total] where you wish to reference [Branch 1] and type in the = symbol, then simply chose the [Branch 1] sheet and the cell you want to reference there and hit the [Enter] key.  Excel will do the work for you and you'll end up with something that looks like:

='Branch 1'!A1

Notice that Excel put the ' mark before and after the sheet name.  That was because it has a space in it.  You can do this type of thing anywhere you need to reference a cell or cells on another sheet.  Lets suppose you want a SUM of cells A1 through A10 from Branch 1; you could type in

=SUM('Branch 1'!A1:A10)

or you could start it off by typing

=SUM(  and then choosing Branch 1 and then cells A1 through A10 and hitting the [Enter] key.  Sometimes you may have to type in the closing ) symbol.

---

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

Answer 3

Hi,

• Select the cell which should hold the formula.
• Enter the equal sign.
• Click on sheet tab of sheet Branch 1.
• Select the appropriate cell. The formula may read: ='Branch 1'!A1
• Press enter

Regards,

Frank

---

If this post answers your question, please mark it for all readers as the Answer or vote if the reply has been helpful.

Answer 4

If I have sheets Branch 1, Branch 2, etc. with the last sheet name Branch Total and if I am in a cell in Branch Total and want to reference the name of sheet 1 (Branch 1) what do I do.

Do you mean you want to link to a cell on that sheet?

Maybe something like this:

='Branch 1'!A1

That links to cell A1 on sheet Branch 1.

--

Biff

Microsoft Excel MVP