How to set up a transition from one cell to a cell of another sheet in the same document in Google docs?

Question

Google Sheets

There are two sheets "Sheet1-Menu" and "Dist2-Ingredients" in the first one the names of the products, in the second their compositions. You need to make a binding so that when you click on the product name, you go to the composition, namely to a given cell on another sheet.
So far, this formula has turned out:
=HYPERLINK(" https://docs.google.com/spreadsheets/d/1nH7KOE-J9V... ";'Composition'!A2) The advantage
is that now the contents of the desired cells of the second sheet.
The downside is that following a hyperlink opens an additional window in the browser (not critical) and that the transition takes place only on the first cell of the sheet (critical).
Apparently, you need to know the right keys to correct the transition. I would be very grateful for help.

Answer 1

You can do so.
Next to each of the products on the menu sheet, create a cell in which the data will be checked (What to do, View Ingredients)
Next, enable change tracking in this column. If the user has selected the "View Ingredients" item, then the script
1. Returns the Selected item to its original state 2. Transfers the user to the " Ingredients
" sheet in the desired cell Product2 , Product3, etc.) Enable tracking in this cell, everything else is similar .
//Function for tracking changes
function onEdit(event) {
var sheet = event.source.getActiveSheet();//Sheet in which the change occurred
var sheetName = event.source.getActiveSheet().getSheetName() // Get the name of the sheet that active
var actRng = event.source.getActiveRange();//Cell in which the change occurred
var Row = actRng.getRowIndex();.//Row in which the change occurred
var Col = actRng.getColumnIndex();//Column in which a change occurred
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");//Sheet where you want to catch changes
//The check is enabled below. Fires if Changes occurred in the first row, in the first column, on sheet "Sheet1" and if this change is not equal to "Where to go"
if (Row == 1 && Col == 1 && sheetName == "Sheet1" && sheet.getRange( "A1").getValue() != 'Where to go' ) {
activat(sheet.getRange("A1").getValue())//Here we go to the change function
sheet.getRange("A1").setValue(' Where to go') //Setting the selection cell to its original state
}
}
//Going function (In this case, it just selects a range, but you can add other functionality)
function activat(sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange("A1:D10");//Which range to select when transitioning
range.activate();
}

Answer 2

In order not to open an additional window, to navigate through the sheets, you need to specify only the sheet code in the formula

=HYPERLINK("#gid=319212120";'Composition'!A2)
To navigate to a specific cell, add &range=A2
=HYPERLINK("#gid= 319212120&range=A2";'Composition'!A2)