Reference a worksheet name in a cell

Originally Posted by teylyn

as JBeaucaire has suggested in the thread you posted in previously, try

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

to return the sheet name to a cell.

This formula works awesome but it only seems to return the name of the current tab. How would I modify the formula to return the name of other tabs.

Background: what I am trying to do is create a workbook for generating a building condition survey. Some properties we survey have multiple buildings. What I want to do is create a new tab for each building and have the building names on the tabs populate the summary page. I hope this clarifies.

Thanks for all the help. This forum rocks.

Originally Posted by rrporto

Hello,
you can do it by using INDIRECT.
e.g: Letīs say you want to bring in sheet1 a value which is in sheet3 / cell A1. A simple way to do it is typing = "=Sheet3!A1" - I know this is not what you want and here comes INDIRECT.

The same way to do it using indirect: =INDIRECT("Sheet"&A3&"!A1") - where A3 points to which sheet carries the information you want. That is: if you want sheet 3 - A3 = 3; if you want sheet 2 - A3=2 ... and so on.

cheers

I have been trying the indirect method, but I keep coming up with #REF in the cell. I am not sure if its the syntax of the formula I am getting wrong if there is something else going on. I have attached an image to illustrate.

I still had trouble getting the indirect to work despite everyone's efforts to help. I was however able to accomplish what I wanted using the following process. I probably took the long road, but I still got there.

1. I used the formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) in cell A1 of each worksheet to call the name of that worksheet.

2. I then gave the cell a name.

3. I then formatted the cell by selecting the custom option under the Number tab and used ;;; to hide the contents of cell A1.

4. On my project summary page I used the named cell for each worksheet to populate the names.

Now when the names of my worksheets change, my project summary worksheet will update accordingly.

Thank you to everyone who assisted me with trying to solve this.

Great forum; I have recently had this question as well.

I am also wondering... my worksheet names are actually dates (Ex. 12-1-11)

I used the formula given: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

and it worked perfectly to reference the worksheet name into a cell of my choice.

Now, if only I could format this date to show up as Thursday, December 1, 2011 instead of 12-1-11...

Any suggestions?

Sorry! I took the time to read the rules today... no more issues!

I know this is an old post but I achieved doing INDIRECT Lookups to Worksheets by concatenating the single quotes in..

i.e. INDIRECT(CONCATENATE("'",$A2,"'")&"!$A$2:$B$600")

Hi Taylyn,
i am using this to find sheets name in sheet1:
=INDIRECT("Invoice "&row(A1)&"!A1") but did not get sheet name in sheet1. can you help me?
Thanks