How do I dynamically reference multiple sheets on Google SpreadSheets?

Question

I use a Google SpreadSheet add-on to make Google Analytics Reports. My problem is that for each query this add-on creates a different sheet. I often have around 50 different sheet with data that must be compiled into a single sheet. For example:

I might have 3 sheets named: "Organic Data", "Direct Data" and "Other Data"

Each sheet has a different set of data that I want to compile into a single table in a 4th sheet called "Report". This table will have in its first column data from the "Organic Data" sheet, in its second column data from the "Direct Data" sheet and in its third column data from the "Other Data" sheet.

The way I´m doing this is by referencing the cells one by one. So in the "Report" sheet I will write =Organic Data!B4 and then drag to fill the other cells of the column, and then I would do this for the other sheets to fill up the other columns of the "Report" sheet.

The problem is that, as I´ve said, I often have more than 50 different sheets and referencing them can get quite hard.

I would like to do this dynamically. Have the names of the sheets on a cell and use that to reference the cells. For example:

In the "Report" sheet:

A1 = "Organic Data", B1 = "Direct Data", C1 = "Other Data"

In cell "Report!A2" I´ve tried writing =CONCAT("=",A1,"!","B4") hoping to get in this cell the value of the cell "Organic Data!B4", but instead I get the string "=Organic Data!B4, and not the value of the cell in "Organic Data" sheet.

I`ve made a custom formula to solve this problem:

function referenceSheet(sheetName, cellInput) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ""
  var cell = sheetName+'!'+cellInput;

  var value = ss.getRange(cell).getValue();

  return value;
}

It works quite well. In my example above to get the value of the cell "Organic Data!B4" into the cell "Report!A2", I need to write on "Report!A2" the following:

"=referenceSheet(A1, cell("address",B4))"

and it will return the value of the cell "Organic Data!B4".

I do have some issues with this custom formula that I´ve made. Mainly, custom formulas don´t refresh onChange, so if I change the value of "Organic Data!B4", "Report!A2" will not change.

I would like to know if there´s a native formula that does something similar to my custom formula?

If not, what´s the best way to make my custom formula to refresh as I change the date from other cells?

Thank You Very Much!

if you're looking for a native or built in formula you should post at the Google Product Forums for spreadsheets: productforums.google.com/forum/?nomobile=true#!categories/docs/… . there are some really heavy hitters there that write formulas to give me headaches. — ScampMichael
And if you end up having to use your custom formula, you can put it in a trigger like onEdit(), so it will be called everytime a value in any cell changes. — Andy

Jussi Jussi · Accepted Answer · 2015-05-06T16:57:45

I´ve posted the same question on Google Forums and got a nice answer.

There is a native formula call =INDIRECT

For my exemple it works like this:

=INDIRECT(CONCATENATE(A$1,"!",cell("address",$B4)),true)

How do I dynamically reference multiple sheets on Google SpreadSheets?

1 Answers