Today we are going to discuss about different methods in Excel to list all sheet names.
When the number of sheets in a workbook exceeds 10, navigating between them can become a cumbersome task. In such cases, a table of contents listing all the sheet names with back-and-forth hyperlinks can be quite beneficial.
Method-1:
This method involves Macro 4.0 function GET.WORKBOOK, and you will be asked to save this file as macro enable workbook.
We can't directly use those Macro 4.0 function in cells, we need to save them in name manager as shown below,
Formula in Refers to section,
=GET.WORKBOOK(1)&T(NOW())
1 in Get. Workbook function helps us to get the sheet names, and this will deliver us in horizontal array. These macro functions are not dynamic, in order to make it dynamic concatenate our macro function with T(NOW()).
Now() is volatile function which will evaluate whenever we press Enter or F9 or switch between sheets. This function will deliver us the current time and T function will help us to convert time into zero length text string ("").
In A1 cell in Index sheet,
=COUNTA(Sh_Names)
COUNTA function counts nonblank cells in array Sh_Names, in our case it delivers us 6.
This is a helper cell; you can hide this by applying font color white or keep it visible.
Formula in B3 cell,
=IF(ROWS($B$3:B3)<=$A$1,REPLACE(INDEX(Sh_Names,ROWS($B$3:B3)),1,
FIND("]",INDEX(Sh_Names,ROWS($B$3:B3))),""),"")
Let me start this from scratch,
INDEX(Sh_Names,ROWS($B$3:B3))
ROWS($B$3:B3) will deliver us 1, INDEX function will help us to pick first element in Sh_Names array. If we drag the formula down, ROWS($B$3:B3) will become ROWS($B$3:B4).
It is an expandable range, now it delivers 2. INDEX function will pick 2nd element in array.
If we evaluate INDEX part, output will look like below,
"[Blog - Sheet names.xlsm]Index"
Workbook name inside square brackets and sheet name.
REPLACE function will help us to delete workbook name and square backets.
Arguments of REPLACE function,
=REPLACE(old_text,start_num,num_chars,new_text)
=REPLACE(INDEX(Sh_Names,ROWS($B$3:B3)),1,FIND("]",INDEX(Sh_Names,ROWS($B$3:B3))),"")
old text is workbook and sheet name from Sh_Name array,
start num is 1,
num chars is position number of "]", to get that relative position, we can use FIND function.
new text is zero length text string which is "".
This entire construction will deliver us the sheet name after ending square bracket.
Method-2:
This is same as method-1 but with the help of LAMBDA Function.
Formula in Refers to Section:
=LAMBDA(Sh,GET.WORKBOOK(Sh)&T(NOW()))
Formula in B3 cell is,
=TOCOL(REPLACE(Sh_Names(1),1,FIND("]",Sh_Names(1)),""))
This is single cell formula, so no need to drag it down and it does the same thing which was explained in method-1.
Bonus Tip to Create Hyperlinks:
Formula in C3 cell,
=HYPERLINK("#"&B3&"!A1",B3)
Comments