How do I lookup sheet names and create hyperlinks to sheets in a workbook?

Question

I am managing a workbook with many sheets and I would like to make a separate sheet for navigation.

The sheets' names have a numeric prefix, like:

000-ATable

001-AnotherTable

002-YetAnotherTable

The sheet names may be subject to changes, so I'd like to use a formula.

What I want:

Number	Link
000	ATable(links to 000-ATable)
001	AnotherTable(links to 001-AnotherTable)
002	YetAnotherTable(links to 002-YetAnotherTable)
003	(returns blank if sheet with such prefix does not exist)
...

How do I do this?

Answer 1

So, here is way using Excel Macro 4.0 and with the help of dynamic functions, note that HYPERLINK() will not work with SPILL.

---

First, need to create a defined names to automatically grab the SHEETNAMES therefore, jump onto Formulas Tab, click on Name Manager and click New, process with the Name as SHEETNAMES and use in refers to :

=TEXTAFTER(GET.WORKBOOK(1),"[")

---

• Formula used in cell B2

=LET(
     _SheetNames, "["&INDEX(SHEETNAMES,ROWS(A$1:A2)),
     IFERROR(TEXTBEFORE(TEXTAFTER(_SheetNames,"]"),"-"),""))

---

• Formula used in cell C2

=LET(
     _SheetNames, "["&INDEX(SHEETNAMES,ROWS(A$1:A2)),
IFERROR(HYPERLINK("#'"&_SheetNames&"'!A1",TEXTAFTER(_SheetNames,"-")),""))

---

Both the above formulas needs to be filled down as well as the workbook needs to be saved as .xlsm or .xlsb

---

Here is a link one can check

---

---

Alternatively, one can get Number as in context of the OP by using SHEETS() function:

=BASE(SEQUENCE(SHEETS()-1,,0),10,3)

---