Join data from present and absent sheets using Google Query formula

0
votes

I have a spreadsheet where I need to join the data of the sheets that exist/ For example my spreadsheet might contain January and March only Or sometimes February and December only Or it can contain all the monthly sheets. Hence the monthly sheets are created and deleted by me.

I am pooling the monthly tab sheets to a single sheet called transactions sheet

=QUERY({January!A:I; February!A:I; March!A:I; April!A:I; May!A:I; June!A:I; July!A:I; August!A:I; September!A:I; October!A:I; November!A:I; December!A:I}, "select * where Col2 <>''")

I currently have January and April sheets but the above formula gives an error since the remaining sheets are absent.

How do I solve this issue to always include the results of whatever monthly sheets are present?

enter image description here

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

You could try something like this. I have written this for 3 months - Jan to Mar.

You would need to repeat the formula pattern till December.

=QUERY({
iferror(indirect("January!A:I"),{"","","","","","","","",""});
iferror(indirect("February!A:I"),{"","","","","","","","",""});
iferror(indirect("March!A:I"),{"","","","","","","","",""})}, 
"Select * where Col2 <>''")

The way it works is

  • indirect() is used to get the range of cells from each sheet
  • iferror() is used to check if indirect() returns an error

If there is an error, i.e. the sheet does not exist, then the 2nd value of iferror() creates a row of blank cells across 9 columns.

That is because in this example, as you are using 9 columns A through I.