How to create INDIRECT array string of multiple sheet references in Google Sheets?

by

dudes who copy-pasted INDIRECT function into Google Sheets completely failed to understand the potential of it and therefore they made zero effort to improve upon it and cover the obvious logic which is crucial in this age of arrays.

in other words, INDIRECT can’t intake more than one array:

=INDIRECT("Sheet1!A:B"; "Sheet2!A:B")

nor convert an arrayed string into active reference, which means that any attempt of concatenation is also futile:

=INDIRECT(MasterSheet!A1:A10)
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{Sheet1!A:B; Sheet2!A:B}")
————————————————————————————————————————————————————————————————————————————————————
={INDIRECT("Sheet1!A:B"; "Sheet2!A:B")}
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}")

the only possible way is to use INDIRECT for each end every range like:

={INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}

which means that the best you can do is to pre-program your array like this if only part of the sheets/tabs is existant (let’s have a scenario where only 2 sheets are created from a total of 4): 

=QUERY(
 {IFERROR(INDIRECT("Sheet1!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet2!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet3!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet4!A1:B5"), {"",""})}, 
 "where Col1 is not null", 0)

so, even if sheet names are predictable (which not always are) to pre-program 100+ sheets like this would be painful (even if there are various sneaky ways how to write such formula under 30 seconds)

an alternative would be to use a script to convert string and inject it as the formula

A1 would be formula that treates a string that looks like real formula:

=ARRAYFORMULA("=QUERY({"&TEXTJOIN("; ", 1, 
 IF(A3:A<>"", "'Week of "&LEFT(A3:A, 5)&"'!A1:D5", ))&
 "}, ""where Col1 is not null"", 1)")

further populating of A6:A will expand the string automatically

then this script will take the string from A1 cell and it will paste it as valid formula into C5 cell:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  
var src = sheet.getRange("A1");
var str = src.getValue(); 
var cell = sheet.getRange("C5"); 
cell.setFormula(str);
}

0

of course, the script can be changed to onOpen trigger or with custom name triggered from the custom menu or via button (however it’s not possible to use the custom function as formula directly)

More Related Contents:

  1. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  2. Selecting the last value of a column
  3. Is there a way to evaluate a formula that is stored in a cell?
  4. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  5. ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?
  6. No permission to call msgBox in Google Apps Scripting
  7. Apps Script: how to get hyperlink from a cell where there is no formula
  8. Periodically refresh IMPORTXML() spreadsheet function
  9. Using built-in spreadsheet functions in a script
  10. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  11. Google Sheet use Importxml error could not fetch url [duplicate]
  12. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  13. .setFormula() and received error missing ) argument list
  14. Determining the last row in a single column
  15. How can I be getting multiple unwanted event blocks from the same onFormSubmit Trigger?
  16. Detect user inserting row or column in a google spreadsheet and reacting in a script
  17. Moving Files In Google Drive Using Google Script
  18. How to get onFormSubmit to trigger automatically?
  19. Copying a spreadsheet copies all linked files as well
  20. “SpreadsheetApp.getUi() cannot be called from this context”
  21. Protect ranges with google apps script
  22. Difference between two dates expressed as years, months, days (in one column)
  23. How to convert a Google Sheets-File to an Excel-File (XLSX)
  24. Dynamically creating spreadsheet menu items
  25. Creating and Sending sheets in the same function
  26. How do I know if spreadsheet cells are merged using google apps script
  27. How to ignore empty cell values for getRange().getValues()
  28. Google apps script error: “You do not have permission to call protect” [duplicate]
  29. Is it possible to create macro buttons to navigate in Google Sheets?
  30. Publish an add-on privately

Leave a Comment