EXCEL PRO TIP: Hyperlinking Between Worksheets

[@wayneedmondson1065]

Hi Chris.. just viewed this again and it inspired me to create a UDF to auto list sheets in a workbook, based on an index, as follows: Function ListSheetNames(IndexNo As Integer) As String Application.Volatile Dim col As New Collection Dim ws As Worksheet For Each ws In Worksheets col.Add ws.Name Next ws ListSheetNames = col(IndexNo) End Function I use this in conjunction with: Private Sub Worksheet_Activate() ActiveSheet.Calculate End Sub ..to make an auto-updating Index sheet that I can then link using your HYPERLINK technique. If I add, delete, change sheet names, change sheet order, etc., the UDF takes care of keeping the list current. It works for me.. thought I would share it. Thanks for the inspiration to create something new. Thumbs up!

[@ExcelMaven]

Nice, that's a great tool!

[@wayneedmondson1065]

@@ExcelMaven Hi Chris.. thanks. I tinkered with it a bit more.. changed from a collection to an array and changed function result to: ListSheetNames = Application.Transpose(arr) and in Insider EXCEL it spills vertically.. so eliminates the need for an index incrementer. Also, shows that UDFs can spill in new EXCEL, taking advantage of the new calc engine. The fun never ends with EXCEL.. haha!! Thumbs up!!

[@wayneedmondson1065]

Hi Chris.. thanks for this tip. I have always done a right click, then Link, then Bookmark to pick the worksheet which then makes the subject text itself the hyperlink. While this works.. no way to copy and or make dynamic. I've also built it out using VBA.. like when creating a table of contents from sheet names. The worksheet function is great.. just like any other formula.. build and copy. Too bad the syntax is so particular, but like anything else, once you get the hang of it.. no problem. Thanks for this great tip.. will definitely use in the future. Thumbs up!

[@ExcelMaven]

Yeah this one takes some getting used to, but can be a great tool once you nail the syntax!

[@Td101_31]

Hi Chris, I have purchased your course on data visualisation, this tip is great thx. I have standard excel 2016 but many of my spreadsheets and dashboard take a very long time to open and close, sometimes the whole program shuts down, I'm attempting to track; environmental, health + safety and quality performance of the company where I work. Am I setting up my data in the wrong way? I hope you are able to help me?

[@ExcelMaven]

Hi Tunde, it's difficult to diagnose without more context, but slow performance is often cause by 1) storing large amounts of data in your spreadsheets, and 2) constantly recalculating many formulas, including processor-intensive functions like arrays, SUMPRODUCT, etc. If you are managing and analyzing large amounts of data from multiple sources, your best bet is to use Power Query to compress that data in Excel's data model (vs. normal spreadsheets), use the Data Model to create relationships between those tables, and then use Power Pivot and DAX to analyze your model without using thousands of redundant formulas. This is all covered in my Intro to Power Query, Power Pivot & DAX course at courses.excelmaven.com. Check it out!