# Thread: List all Sheet Names

1. Excel 2003

Does the below formula list all the sheet names in a workbook?
I can't get it to work

=ISNUMBER(ROWS(INDIRECT("'"&WorksheetNameHere&"'!A 1")))

TIA

2. No, the formula returns either TRUE or FALSE.

3. [quote name='HansV' post='773540' date='03-May-2009 16:45']No, the formula returns either TRUE or FALSE.[/quote]

Does a technique exist which returns the name of a worksheet?

4. If you want to return the name of the worksheet to be returned in a cell, you can use the formula

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

where A1 can be any cell on the worksheet. This formula will only work if the workbook has been saved to disk; it'll return an error value in a new workbook that hasn't been saved yet.

5. I use the following to create a Table of content list of sheets. I insert a blank worksheet as the first sheet in the book and run the following macro. I've commented out the line that add a hyperlink to the A1 cell in

Code:
Sub ListSheets()
Dim i As Integer
With Cells(1, 1)
.Value = "Sheet Name"
.Font.Bold = True
End With

For i = 2 To Worksheets.Count
With Cells(i, 1)
.Value = Worksheets(i).Name
End With
Next

Cells(1, 1).EntireColumn.AutoFit
End Sub

6. Excel 2003
Does the below formula list all the sheet names in a workbook?
I can't get it to work
Return a list of worksheet names, try to use ……...

1) Define (Insert > Define > Name) the following...

Name : WorksheetsName

Refers to, enter :

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

Click OK

2) Then try the following formula...

=IF(ROW(1:1)>COUNTA(WorksheetsName),"",INDEX(Works heetsName,ROW(1:1)))

Regards
Bosco

7. [quote name='bosco_yip' post='773573' date='04-May-2009 10:11']Return a list of worksheet names, try to use ……...

1) Define (Insert > Define > Name) the following...

Name : WorksheetsName

Refers to, enter :

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

Click OK

2) Then try the following formula...

=IF(ROW(1:1)>COUNTA(WorksheetsName),"",INDEX(Works heetsName,ROW(1:1)))

Regards
Bosco[/quote]

Great! This did the trick

8. [quote name='HansV' post='773560' date='04-May-2009 06:42']If you want to return the name of the worksheet to be returned in a cell, you can use the formula

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

where A1 can be any cell on the worksheet. This formula will only work if the workbook has been saved to disk; it'll return an error value in a new workbook that hasn't been saved yet.[/quote]

Hans

Not sure how this works. Its shows only Sheet1 but not others

9. [quote name='bosco_yip' post='773573' date='03-May-2009 22:11']Refers to, enter :

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())[/quote]
Thanks for that Bosco

Can you help me understand what's going on with the formula we place in the 'Name' > 'Refers to' box?

T.I.A.

10. GET.WORKBOOK is an old Excel 4 macro function. It can't be used in cell formulas, but it does work in defined names.

The help file for Excel 4.0 Macro Functions is still available from Microsoft: Macrofun.exe File Available on Online Services.

11. This formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)+ will display the name of the sheet containing the cell with the formula.

You could replace A1 with a reference to a cell on another sheet (for example MyOtherSheet!A1) but that kind of defeats the purpose.

12. Copy the formula down a enough rows to cover the number of sheets in the workbook.

#### Posting Permissions

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