5

In sheet1 of my Google sheet in A3 I have this formula:

=sort (query({Sheet2!A3:F;Sheet3!A3:F;Sheet4!A3:F;Sheet5!A3:F},), 1, true)

This is displaying all A3:F information from all the sheets listed between {} - which is good.

What I would like to do is to have in A2 the following content:

{Sheet2!A3:F;Sheet3!A3:F;Sheet4!A3:F;Sheet5!A3:F}

And use it in A3 like the following:

=sort (query(<content of A3>,), 1, true)

Why? Because I would like to dynamically create the "sheet selection" from another formula & function that is 'extracting' all the sheets names (or tabs) of a Google sheet. This way, if another sheet (or tab) is created, say Sheet6, value of A2 will automatically change to:

{Sheet2!A3:F;Sheet3!A3:F;Sheet4!A3:F;Sheet5!A3:F;Sheet6!A3:F}

and will update the 'query' of A3

I can't figure out how to include A2 value into formula in A3.

4

First, you don't need query in your formula, it should be simply

=sort({Sheet2!A3:F; Sheet3!A3:F; Sheet4!A3:F; Sheet5!A3:F}, 1, true)

Unfortunately, it is impossible for such a formula to take a string parameter {Sheet2!A3:F; Sheet3!A3:F; Sheet4!A3:F; Sheet5!A3:F} from another cell. Normally one would use indirect(A2) for this, but indirect does not support array notation {...}, it only works with individual ranges like indirect("Sheet2!A3:F"). Using {indirect("Sheet2!A3:F"); indirect("Sheet3!A3:F")} is possible but this doesn't help with your problem.

Suggestion: since you will need a script to generate the list of all sheets anyway, use it to create the sort formula directly. Here is a function that does this. It gets all sheets, gets their names, excludes certain sheets (including the "masterSheet" where the formula will be located, to avoid cyclical dependency), appends the range to each sheet name (A3:F here), single-quoting sheet names for safety, and then puts all this in the sort formula in cell A3 of masterSheet.

function combineSheetsFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = "A3:F";
  var masterSheet = "Sheet1";
  var excluded = ["Sheet1", "Sheet2", "Sheet3"];
  var sheetNames = ss.getSheets().map(function(sheet) {
    return sheet.getSheetName();
  }).filter(function(name) {
    return excluded.indexOf(name) == -1;
  }).map(function(name) {
    return "'" + name + "'!" + range;
  });
  var formula = "=sort({" + sheetNames.join(";") + "}, 1, true)";
  ss.getRange("'" + masterSheet + "'!A3").setFormula(formula);
}

This function combineSheetsFormula should be launched as explained here, via a function sheetChange that is triggered "on change" and launches the formula function if the change was adding or deleting a sheet.

function sheetChange(e) {
  if (e.changeType == "INSERT_GRID" || e.changeType == "REMOVE_GRID") {
    combineSheetsFormula();
  }
}
  • Solution works fine ! I see how you exclude the Sheet1 (mastersheet). but how to exclude more sheets than Sheet1 ? (ex: Sheet1, Sheet20, Sheet21) – JCB Nov 30 '17 at 14:04
  • Updated with array excluded. – user135384 Nov 30 '17 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.