EXCEL PRO TIP: Hyperlinking Between Worksheets

For access to all pro tips, along with Excel project files, PDF slides, quizzes and 1-on-1 support, upgrade to the full course (75% OFF):
courses.excelm...
FULL COURSE DESCRIPTION:
This course is NOT an introduction to Excel.
It's not about 101-style deep dives, or about showing off cheesy, impractical "hacks".
It's about featuring some of Excel's most powerful and effective tools, and sharing them through crystal clear demos and unique, real-world case studies.
We'll cover 75+ tools & techniques, organized into six categories:
-Productivity
-Formatting
-Formulas
-Visualization
-PivotTables
-Analytics
Demos are self-contained and ranked by difficulty, so you can explore the content freely and master these tools and techniques in quick, bite-sized lessons.
Full course includes LIFETIME access to:
-10+ hours of high-quality video content
-Downloadable PDF eBook
-Excel project files (including data sets & solutions)
-1-on-1 expert support
-100% satisfaction guarantee (no questions asked!)
Happy analyzing!
-Chris (Founder, Excel Maven)

[@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!