Extract a List of Worksheet Names Using Functions - With 2 ways Navigation

In this tutorial I show you how to create a list of All sheet names in any workbook by using a Macro language function (=Get.Workbook) inside a Defined name. I will then combine the Defined name with an INDEX function, a ROW function, a REPLACE function and an IFERROR function.
Then we create a Forward and Backward Navigation from the Index of Sheets to any other sheet and back, using a HYPERLINK function.

You can Download the Start File and follow along by clicking on the link:

There is a quiz Question for you to answer at 9:00 min... give it a try and write your answer in a comment.

There are two other methods for creating a List of Sheet Names, either by using a VBA code or by using Power Query. I sincerely encourage you to watch these 2 other methods and write in a comment, which of the 3 methods you like most. Here are the links to the other methods:

Tutorial for Using Power Query

Tutorial for Using a VBA code

If you find value in this tutorial give it a thumb up, and hit the “Subscribe” button to be notified when new videos are posted.

[mohammadmohammad-yvmd]

When Nabeel explains the thing, it becomes easier either of all the ways!!!
Commendable 👏👏👏

[SuperOnur]

I have 4 different solutions for the quiz question:

1) By using the same index function we created with the same defined name;
=COUNTA(INDEX(MySheets, 0))
2) We can define a new name via name manager similar to before. Let's call it as "CountSheets" coming with formula Get.Workbook(4)
=INDEX(CountSheets, 0)
3) Select any cell you want, type "=CountSheets" formula and then hit Enter =)
4) The formula below also gives the count of total number of sheets in the workbook as well;
=Sheets()

By the way, your whole tutorials are really great and I'm grateful for that. Thank you very much ;-)

[lonniepeterson]

Exceptionally useful for large spreadsheets with multiple worksheets. I have a very large Budget spreadsheet for our businesses and have been forever clicking back and forth between worksheets... this is so useful in saving time. Thanks for providing this.

[gopalakrishnarao]

Excellent. Very informative. Interesting, inspiring, revealing, thought provoking, realstic, lucid & candid..Thanks a lot.

[erikguzik]

Awesome video. VERY well explained. I know this video is a few years old, just some update info for any Office 356 Users, here is how you can make the list that was created in Col(A) instead of manually copying the formula. It will also always show the number of sheet names you have. =TEXTAFTER(INDEX(MySheets, SEQUENCE(SHEETS())), "]")

[educacionportable]

Wow beautiful way to teach! Thank u so much master for share your knowledge. God bless u more!

[shaniwafa]

Mr. Nabil,
It is very nice to see your videos. I used to watch your all available updates. I like your way of presentation of the subject and specially your accent. Thanks for your all endeavors for educating one who loves the excel.
Great job
Shaniwafs

[stevew]

Wonderfully put across: that was the best 15.43 minutes I have spent on YouTube. I am new to your channel and I am grateful for clear, step by step explanations. Thank you.

[iankr]

Very clear and concise explanation. Many thanks!

[lawrindaquaye]

wow! Good teacher, this has really help me. Thank you very much

[mohitjagad]

Superb.. Much informative & useful..
Thanks for sharing.

[nooramin]

Its just amazing my friend, I am speechless. Thanks a lot

[zaighamuddinfarooqui]

Awesome. In my opinion PQ's method is best.

[Luciano_mp]

Awesome, very cool, very useful. Thank you!

[fabricegemo]

Very helpful. Thanks a lot. One request though. What do I have to do in order to have the list dynamically, in the sense that any new sheet tab that appears afterwards, the List of Sheets to update automatically?!

[nadermounir]

Very nice video. Thank you for your hard work 👍

[satchuarchary]

Awesome
Thank You for these tips.
Also wanted to know whether you can also add a column to show the status of the sheet eg; Visible or Hidden
Thank you once again

[vitarathiel]

cool tutorial !
how to make the list sheets horizontaly ?

[azherrafiq]

i want to create main worksheet with specific data and dates i.e court cause list. dates are changed/adjourned dates as required .if next date is added or change it may create automatic cause list on worksheet as per date ...and it collect data from main worksheet as per date entry. .your reply will be appreciated . thanks

[zbigniewkowalczyk]

thanks for that. followed steps I managed to create links between different worksheets. but cant get automatic update to the list when I ad new worksheet or I change name of old ones. did I miss anything ? thanks