How To Link Data to Another Tab in Google Sheets

How to link to another tab in Google Sheets-1

Linking within spreadsheets is a useful way of showing your workings out or linking to other datasets within the same workbook. Hyperlinking is also useful for more complicated spreadsheets where one sheet is the master that links out to the subordinate sheets. As Google Sheets is browser based, normal hyperlinking can achieve what you’re looking for. Here’s how to link to another tab in Google Sheets.

I’ll also show you how you can link one cell with a different workbook to import data as that can be useful too.

How to link to another tab in Google Sheets-2

Link to another tab in Google Sheets

There are two ways that I know of to create a link between sheets in the same workbook. The first is simple while the second involves more steps but allows more customization options. First, let’s look at how to get the data to create the link.

If you look in the URL bar of each tab within a Google Sheet you will see that it has a unique URL. In my example, the main sheet has the URL of: ‘https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE/edit#gid=1168914286’

My second sheet within the same workbook has the URL of:

‘https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=498267371’

For the purposes of linking, we are interested in that final part of the URL, the ‘gid’ code. The URL up to that point is shared by each workbook. The ‘SOMEGIBBERISHCODE’ part is identifiable as the workbook, the gid code identifies each individual sheet. So to link to another tab within a sheet, you need to know the gid code of the destination tab.

Once you know that, you can create the link.

How to link to another tab in Google Sheets-3

First the easy way to create a link.

  1. Select an empty cell in your worksheet.
  2. Select Insert and Link. A popup box should appear by the selected cell.
  3. You can either paste the URL into the box or select Sheets in this spreadsheet and manually select the sheet to link. You can also select a range of cells to link.
  4. Highlight the cell with the link and rename it. The link will take on the name of the tab you’re linking to which might not work for your needs. Change it here.

Click the hyperlink and you should see a small blue box containing the link. Click it again to go to a different sheet.

The other way is the manual way but gets you to the same place.

  1. Select a cell to put the hyperlink within your sheet.
  2. Add ‘=hyperlink(“https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=DESTINATIONTGID”, “Link Name”)’ to the formula bar.
  3. Click the link and a popup bubble appears with a small blue link box, click that to test the link.

Where you see ‘DESTINATIONTGID’ add the gid code of the tab you want to link to. In the example above, I would put ‘gid=498267371’ to link to that specific tab. Yours will obviously differ. You can then name the link whatever you like. Just change ‘Link Name’ to something meaningful.

As Google Sheets is browser based, when it calls the different tab, it looks like it is opening another browser tab and apparently that is what it’s doing, except it is within the same active tab. I don’t know either. All I know is that there is a short delay when linking as if you were visiting any website through a link.

How to link to another tab in Google Sheets-4

Link to another workbook in Google Sheets

You can also link to other workbooks in Google Sheets. It uses a slightly different method, IMPORTRANGE. This presumes that you want to pull data from one workbook to another within a given range.

IMPORTRANGE(“https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=DESTINATIONTGID”, “sheet1!A1:C10”)

In this example, we import cells A1 to C10 in Sheet1.

When you first apply this condition, you will be prompted to allow the sheet access to the other sheet. Once you grant permission to access and/or change data within the destination sheet, the import should work right away. Once granted, the permission remains until you tell it otherwise or one or the other sheets is moved or deleted.

You can add multiple IMPORTRANGE requests simultaneously. For example:

IMPORTRANGE(“https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=DESTINATIONTGID”, “sheet1!A1:C10”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=DESTINATIONTGID”, “sheet1!A1:C10”)

Or:

IMPORTRANGE(“https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=DESTINATIONTGID”, “sheet1!A1:C10”);(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/SOMEGIBBERISHCODE /edit#gid=DESTINATIONTGID”, “sheet1!A1:C10”)

Note the top example uses a comma to separate the two. This will display data side by side in the target sheet. The second example uses semicolon which will display the data one above the other. Thanks to the Google Docs Help Forum for that one. I would never have been able to figure that one out!

Got any other ways to link Google Sheets or workbooks to each other? Tell us about them below if you do!

Posted by Jamie on February 23, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *