# Use content of a cell as part of a formula in another cell

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.

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 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