Thanks:  0
Likes:  0

# Thread: worksheet name in cell

1. is there a way to insert the worksheet name into a cell
EG:
sheet 1 name is "John"
sheet 2 name is "brian"

on another worksheet "summary" i want to insert these two worksheet names in cells a1 and a2.

regards

2. I don't know if this is the easiest way, but this formula seems to work:

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

(Change the 20 at the end of the formula to the maximum length you expect the sheet name might be.)

3. =RIGHT(CELL("Filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
and another

Dave

4. these work, but what i would really like to do is be able to drag down so it finds the next worksheet name, and the next, etc.....
i have 26 sheets in total

5. On 2002-04-09 04:42, imillar wrote:
these work, but what i would really like to do is be able to drag down so it finds the next worksheet name, and the next, etc.....
i have 26 sheets in total
Some other answers here which may assist you.
http://www.mrexcel.com/board/viewtop...c=3816&forum=2

6. ## Re: worksheet name in cell

I would like to offer a better, more versatile, version of the formula above: MID(CELL("filename"),FIND("]",CELL("filename"))+1,20)

If you copy this formula in 10 different sheets, all 10 formulas will return the result of the ACTIVE sheet. Instead:

MID(CELL("filename",a1),FIND("]",CELL("filename",a1))+1,20)

This formula will return the result of the sheet where the formula exists.

Excel Magic Trick 756: Create Sequential Dates Across Sheets & Extract Sheet Name To Cell - YouTube

7. ## Re: worksheet name in cell

With a macro?

Code:
```Sub Summary()   Dim ws As Worksheet, n As Integer
Application.ScreenUpdating = False
n = 1
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Summary" Then
Sheets("Summary").Range("a" & n) = ws.Name
n = n + 1
End If
Next
Application.ScreenUpdating = True
End Sub```

8. ## Re: worksheet name in cell

Sorry that came out badly on on the edit.

Code:
```Sub Summary()
Dim ws As Worksheet, n As Integer
Application.ScreenUpdating = False
n = 1
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Summary" Then
Sheets("Summary").Range("a" & n) = ws.Name
n = n + 1
End If
Next
Application.ScreenUpdating = True
End Sub```

9. ## Re: worksheet name in cell

Suddenly realised this thread is 10 years old???
Nice to know that people still like offering solutions but....

10. ## Re: worksheet name in cell

Originally Posted by imillar
these work, but what i would really like to do is be able to drag down so it finds the next worksheet name, and the next, etc.....
i have 26 sheets in total
Originally Posted by econocrat
I would like to offer a better, more versatile, version of the formula above: MID(CELL("filename"),FIND("]",CELL("filename"))+1,20)

If you copy this formula in 10 different sheets, all 10 formulas will return the result of the ACTIVE sheet. Instead:

MID(CELL("filename",a1),FIND("]",CELL("filename",a1))+1,20)

This formula will return the result of the sheet where the formula exists.

Excel Magic Trick 756: Create Sequential Dates Across Sheets & Extract Sheet Name To Cell - YouTube
The length of 20 is retrictive. I think a sheet name can have 31 chars, the max length allowed. The following does not require such a specification:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

By the way, the OP wants to list all sheets...

Define Sheets by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Invoke now in A1 on Summary...

Either:

=IF(ROWS(\$A\$1:A1)<=COUNTA(Sheets),INDEX(Sheets,ROWS(\$A\$1:A1)),"")

Or:

=IFERROR(INDEX(Sheets,ROWS(\$A\$1:A1)),"")

## User Tag List

#### Posting Permissions

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