How To Create A Dynamic Hyperlinked List Of Worksheets In Excel

In this tutorial we are going to see how we can create a list of worksheets in our workbook, automatically create a hyperlink to each worksheet and make it dynamic so that when we add or delete any worksheet, the hyperlinked list will automatically update.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

In our workbook we have several worksheets:

I want to create a list of these on my Index worksheet, like this:

And from that create a list of hyperlinks that will dynamically adjust if I make any changes to them, such as add, delete, or change the names, like this:

We’ll use this named formula:

We’ll use this formula to create the list of worksheet names:

=IFERROR(INDEX(MID(SheetList,FIND(“]”,SheetList)+1,100),ROW(B1),1),””)

And this one to create the hyperlinks:

=IFERROR(IF(B2=”Index”,””,HYPERLINK(“#”&”‘”&B2&”‘!$A$1″,INDIRECT(B2&”!$B$1″))),””)

Two important things to note:

1) This will not work if you have spaces in your worksheet names. Therefore, if you must simulate spaces, use an underscore (e.g. red_name)
2) The workbook must be saved as a macro-enabled workbook (.xlsm). This process will not work in a regular Excel workbook.

Take a few minutes and watch the video to see how this very beneficial process can work for you!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , , , ,

One Response to How To Create A Dynamic Hyperlinked List Of Worksheets In Excel

  1. M. A. Jogpur August 16, 2017 at 10:59 pm #

    Super

Leave a Reply