Google sheets link cell to another tab

I have a Google Sheets workbook with more than 70 tabs, or sheets, in it. In one of them, I have a master list of all of them. For better reference, in the tab "PropertyID", and the column F, from 2 and downwards, I have the name of each other sheet in the workbook. I want to find a way to hyperlink each sheet into the PropertyID tab, so that if someone presses a link in this main "menu", it'd take them to the specific sheet. So far, I've been making a comment on each sheet, and using the "Link to comment" link to do what I want, but this process is tedious, must be done manually, and if I have to download the workbook to excel for easier editing, and then back to sheets, all the coments are turned into 'notes' and the links are lost. Any way I could accomplish this?

asked Sep 21, 2016 at 16:22

Here is a forum which is similar to your concern.

[Source.]

When you switch to a different sheet in Google Spreadsheets, pay attention to the URL in your browser's address bar. At the end of the URL you should see something like:

#gid=0

This number changes when you switch sheets, and specifies which sheet to display. Copy the entire URL and create a hyperlink to it with this formula:

=hyperlink("https://docs.google.com/spreadsheet/ccc?key=0AsaQpHJE_LShcDJ0dWNudHFZWVJqS1dvb3FLWkVrS0E#gid=0", "LINK TEXT")

You can also see in the link provided above how to do it using scripts. "This menu item will open a panel with a list of names of all the sheets in the current spreadsheet. It doesn't look like it, but if you click on one of the sheet names, that sheet will come to the front."

Here is a related thread which might also help:

• How do hyperlink from one tab to another?

Hope this helps!

answered Sep 23, 2016 at 1:00

1

Non-formula hyperlinks are now officially supported in Google Sheets, so you now have two more ways of doing this.

1. The manual way: select a cell, insert a link (Insert > Insert Link or Ctrl+K), and add a link to the target sheet #gid=<sheet id>. Unlike HYPERLINK formulas, the sheet will open in the current tab.

2. The automatic way: create a script (Tools > Script Editor) and use setLinkUrl() to link to a sheet.
   For instance, the following function iterates through all cells in column A of sheet "Foo" starting at the 2nd row, and assigns links based on the text of the row.

   function linkRange() {
     const startRow = 2,  // Start at the second row.
           column   = 1;  // Add links to column A.
   
     const spreadsheet = SpreadsheetApp.getActive(),
           sheet = spreadsheet.getSheetByName("Foo"),
           lastRow = sheet.getLastRow();
   
     for (let row = startRow; row <= lastRow; row++) {
       const range         = sheet.getRange(row, column),
             richTextValue = range.getRichTextValue(),
             targetSheet   = spreadsheet.getSheetByName(richTextValue.getText());
   
       if (targetSheet !== null) {
         const sheetId = targetSheet.getSheetId(),
               builder = richTextValue.copy().setLinkUrl(`#gid=${sheetId}`);
   
         range.setRichTextValue(builder.build());
       }
     }
   }
   

   Note that it will discard existing links and formulas in that column.

answered Jun 29, 2020 at 8:57

GregGreg

1213 silver badges8 bronze badges

How about this:

If the sheets are dynamically created/inserted, you can insert the #gid automatically.

EDIT 1 Sheet ref can be reduced to #gid; added filter

=ArrayFormula(
    SORT(
        FILTER(
            HYPERLINK("#gid="&Analyser!H4:H,Analyser!G4:G),Analyser!H4:H>0)
    )
)

answered Nov 2, 2020 at 4:38

Chris GlasierChris Glasier

7932 gold badges10 silver badges20 bronze badges

How do I automatically link data from one sheet to another in Google Sheets?

How do I link cells in different worksheets?