I’m working with a Google Sheets file that has around 70 different worksheets. I created a main navigation sheet called “PropertyID” where column F (starting from row 2) contains the names of all the other sheets in my workbook.
I want to turn these sheet names into clickable links so users can jump directly to any specific worksheet from this master list. Right now I’m using a workaround where I add comments to each sheet and then link to those comments, but this method has several problems. It takes forever to set up manually for so many sheets, and when I export the file to Excel and import it back to Sheets, all my comments become regular notes and the navigation links stop working.
Is there a better way to create direct links between sheets that won’t break when converting between Google Sheets and Excel? I need something more reliable than the comment method I’m currently using.
I’ve dealt with similar navigation challenges in large workbooks and found that Google Apps Script can automate this process entirely. You can write a simple script that loops through all sheets in your workbook and automatically generates hyperlinks in your PropertyID sheet. The script would use something like SpreadsheetApp.getActiveSpreadsheet().getSheets() to get all sheet names, then create HYPERLINK formulas programmatically. This eliminates the manual setup time for 70 sheets and you can re-run the script whenever you add new worksheets. The generated hyperlinks use standard sheet references so they maintain better cross-platform compatibility than comment-based navigation. Takes about 10 minutes to set up the script but saves hours of manual work.
You can use the HYPERLINK function with a direct sheet reference instead of relying on comments. The formula would be =HYPERLINK("#gid=SHEET_ID","Sheet Name") where SHEET_ID is the unique identifier for each sheet. You can find the sheet ID in the URL when you select a sheet - it’s the number after “gid=”. This method creates proper internal links that should maintain better compatibility when moving between Google Sheets and Excel. The setup is still manual but more reliable than comments since it uses native spreadsheet functionality rather than annotation features that get lost in conversion.
another option is using the INDIRECT function combined with hyperlink - something like =HYPERLINK("#'"&F2&"'!A1",F2) where F2 has your sheet name. this creates links using sheet names directly instead of gid numbers which can be easier to manage when you have 70 sheets to deal with