View Full Version : List all Worksheet Names Using Formula
Admin
08-14-2011, 03:41 AM
Hi All,
Here is a method to list all sheet names in cells using formula.
Hit Ctrl + F3, New | Name : SheetNames
Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
In A2 and copied down (Prior to XL 2007),
=IF(ROWS($A$2:A2)<=COUNTA(SheetNames),INDEX(SheetNames,ROWS($A$2:A2) ),"")
XL 2007 and later:
=IFERROR(INDEX(SheetNames,ROWS($A$2:A2)),"")
Enjoy !!
Excel Fox
08-16-2011, 12:30 AM
Attached sample examples for pre Excel 2007 and post Excel 2003 versions.
Here's (http://excelpoweruser.blogspot.com/2011/08/get-all-worksheets-name-by-array.html) a VBA version
Rajesh Kr Joshi
09-28-2011, 08:15 PM
This is simply awesome :)
Thanks
Rajesh
Admin
09-28-2011, 09:15 PM
Hi All,
Here is a UDF.
Function SHEETNAME(Optional ShtOrder As Long = 0, _
Optional ByVal IncludeHiddenSheet As Boolean = False) As Variant
'// Developed by Kris @ ExcelFox.com
Dim ShtCount As Long
Dim i As Long
Dim n As Long
ShtCount = ThisWorkbook.Worksheets.Count
If ShtOrder = 0 Then
SHEETNAME = ActiveSheet.Name
Exit Function
End If
SHEETNAME = CVErr(xlErrRef)
Application.Volatile
With ThisWorkbook
If IncludeHiddenSheet Then
If ShtOrder > ShtCount Then Exit Function
SHEETNAME = .Worksheets(ShtOrder).Name
Else
For i = 1 To ShtCount
If .Worksheets(i).Visible = -1 Then
n = n + 1
If n = ShtOrder Then
SHEETNAME = .Worksheets(i).Name
Exit Function
End If
End If
Next
End If
End With
End Function
use like
Excluding hidden sheets
In A2 and copied down,
=SHEETNAME(ROWS($A$2:A2))
Include Hidden sheet
In A2 and copied down,
=SHEETNAME(ROWS($A$2:A2),TRUE)
To get activesheet name
=SHEETNAME()
Hope this helps !
anelco
07-27-2012, 09:02 PM
hello,
So i have a workbook with 35 sheets
im trying to make a formula which will make a list of all sheets in that workbook, in a new tab.
i would need a step-by-step explanation on how will that work, as im new in this. Thank you
Excel Fox
07-27-2012, 09:30 PM
In your workbook, insert a code module. Copy Admin's code from above, and paste it in the newly inserted code module. Then follow Admin's guideline on how to use this.
KingTamo
12-17-2012, 02:47 AM
great function
just a little bug
I want to get rid of Ref error
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.