Google Spreadsheet Navigate to another Sheet

Question:

Hello. I am trying to link cells in in one Google spreadsheet to cells in another, related sheet. Which is to say, I would like to not only pull data from sheet 1 to populate sheet 2, ideally, I would be able to click on the data in sheet 1 and it would take me to that cell in sheet two. Is this possible?


Solution:

As of now (June 2013), you can't directly set any cell to navigate you to another sheet. But I will show you the work around of doing so.

First of all insert an image, which can be of any size, for this example I have made an image of small size which looks like a button. To insert image, go to "Insert" Menu then select "Image..."

Have a look at the screenshot below:

Now, to move this button anywhere, just right click on it and drag it to your desired position. And also now you have to assign script to it, for that again right click on it and the select "Assign script...".

After that you have to give the function name. You have to write same function name in your script editor.

I have named it "myFunction". And now you have to go to script editor and make a new script. Delete all the existing code in the script file and insert the following code:

function myFunction()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  ss.setActiveSheet(sheet).setActiveSelection("D5");

}

In above code, you can see I have used to navigate to Cell D5 of "Sheet2", which you can change as per your requirement. But keep the function name same as that you have inserted in the "Image" >> "Assign script.."

That's it, now you are almost done. For the first time run the script function "myFunction" from script editor. And then you can click on the image file, and by clicking on it you can navigate to "Sheet2" Cell D5.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive