Advanced formula in Google Sheets

Question

I'm trying to make a worksheet that gives graphs of data in other worksheets. I had the idea that the user could choose which worksheet they want to use.

For example, there are 3 worksheets with data Test1, Test2 and Test3. Now the user wants to see in the overview worksheet the graphs from the data of Test1.

What is the best way to achieve this in Google Sheets without extra work for the users?

Answer 1

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.