# Thread: Worksheet name reference

1. I'm sure that this topic has been covered before, but my search didn't find what I need.

We have a column with the names of the worksheets in the next column we want to put a formula to use the worksheet name from the column A and the same cell C4 from each worksheet, where the client name is typed. I know that I have used a cell value to get me the worksheet name in a formula but can't remember how to do it.

2. You can use the INDIRECT function for this. For example, if the worksheet names start in A2, enter the following formula in B2:

=INDIRECT("'"&A2&"'!C4")

and fill down as far as needed.

3. Thanks, Hans.

Is there are formula to get the worksheet tab name into a cell?

4. The formula

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

will return the name of the sheet containing the cell with the formula.

5. Thanks again, Hans.

Can you explain the formula to me? What does the +1 and the 256 mean in the formula?

6. CELL("filename",A1) returns a reference to the worksheet including the complete path of the workbook that contains cell A1, like this for example:

C:\Excel\[Book1.xls]Sheet1

(See CELL - Excel - Microsoft Office Online)

FIND("]",CELL("filename",A1)) returns the position of the ], i.e. the character immediately before the sheet name.

FIND("]",CELL("filename",A1))+1 is the position of the character after the ], i.e. the first character of the sheet name.

MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) returns the substring that starts at the first character of the sheet name, with length 256. Since the sheet name is at most 31 characters long, 256 is more than enough to extract the entire sheet name (you could use 31 instead of 256).

7. Thanks, Hans.

Sometimes the formulas just need a little translation to get them to make sense, at least to me.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•