I have a complicated Google spreadsheet with many sheets and a table of contents. Is there some way to create a link to the sheet names so that with a click one can go directly to the sheet? That is: clicking on the cell "sheet5" switches to sheet5?
migrated from superuser.com Mar 21 '12 at 16:38
This question came from our site for computer enthusiasts and power users.
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:
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:
With a script
I've thought about this question a lot since I first wrote this answer, and I came up with a solution that involves a script.
With the spreadsheet open, click the Tools menu, then Script editor.... Paste all this code into the editor:
Save the script, then refresh the spreadsheet. After a second or two a new menu, Tasks, will appear after Help. There is one item in this menu: Go to sheet...
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.
As an answer to another question, this script was improved to include a scrollable view and buttons.
|show 6 more comments|
The best way to do this from my experience is to tie a function to a button/image. The only drawback is that you cannot pass parameters along with a script assigned to a button. Due to this you will need to make a function specific to each navigation but they can call be a in a single script file.
Create an image (Insert -> image) and style it to your preference
Create a custom function with the following:
and then a function specific to your button
Finally assign this function to your button
You should now be able to click on your button and navigate to "Sheet5". This can also be modified to go to a specific area of the sheet as well.
protected by Community♦ Jul 20 at 7:14
This question is protected to prevent "thanks!", "me too!", or spam answers by new users. To answer it, you must have earned at least 10 reputation on this site.