Google-sheets – How to stop Google Sheets from opening new tabs

You want to enter a link in a Google spreadsheet.

The syntax of the command is: =hyperlink("url";"Phrase")

The key is that the Google spreadsheet you want to open has a URL.

Let's assume your spreadsheet has the id 1wJR0uH0v0KPex4uoma9Gy2IHCW5JnvQ-QWa6V1GtN5g (you can obtain the id of your spreadsheet by looking at its URL in your browser: it is between …/spreadsheet/d/ and /edit…). Let's assume your spreadsheet contains the sheets:

• overview
• test1, test2, test3
• hidden (you'll add this last worksheet and hide it before you pass your spreadsheet around)

Use data validation for the upper left cell of overview (overview!A1) to only allow the names of your three test worksheets to appear. This input cell will allow the user to select the data to display. In the upper left cell of your hidden worksheet, use importrange to import the data of the selected test worksheet. I.e. set hidden!A1 to the following formula:

=IMPORTRANGE("1wJR0uH0v0KPex4uoma9Gy2IHCW5JnvQ-QWa6V1GtN5g","" & 'overview'!A1 & "A:ZZ")

Remember, replace this id by the one for your spreadsheet. The above formula assumes you have data only within column A and column ZZ. You'll initially get an error for this formula and it will ask you to allow access to the spreadsheet. Just do it.

Once you have done that, create the graph you want within the overview sheet, by pulling its data from the hidden sheet. I assume your 3 data sheets follow the same structure and thus can lead to similar graphs.

After hiding the hidden sheet, you can then pass your spreadsheet to other users who will be able to select the graphed data from cell A1 in the overview sheet.