sorry to be late to this thread, but I saw your question and I thought this might be helpful for you for future reference. You would have to update your formula, but only in one place. While I know that is not ideal, it would be helpful. It may also be possible to build something that can dynamically pull together this needed string to reference the sheets for the array formula. I am not sure if that would work or not.
In Google Sheets you can use the array formula to do this. NOTE: It is very important that the sheets do indeed have the exact same structure. Same columns, in the same position, in every sheet. I've done this numerous times with the query function. It is quite easy if you know how to write a simple select statement.
One really important element of this to keep in mind is that once you use a function to 'obscure' the data (importrange, arrays, or nesting queries are some examples) you must stop referring to the columns in your select statement as letters, and switch to a column reference format. So, column A becomes Col1, B = Col2, etc etc.
Here is an example you can use:
=ARRAYFORMULA(query({sheet1A:D;sheet2A:D;sheet3A:D},"select Col1,Col3 where Col4 is not null"))
Col4 would be a number field in this case. If it was a text field you could use the below - this is actually a real one I used in a spreadsheet, with the names of the sheet changed:
=ARRAYFORMULA(query({'Sheet 1'!A2:Q;'Sheet 2'!A2:Q;'Sheet 3'!A2:Q;'Sheet 4'!A2:Q;Sheet5NoSpaces!A2:Q;'Sheet 6 - Some Addtl Desc'!A2:Q},"select * where Col1<>''"))
There are a plethora of ways you can calculate your stats using the query function as well, so for your use that might be great.
Check the [reference document][1]
for more
If anyone knows if you can write something like:
=ARRAYFORMULA(query({[STRING FROM FUNCTION]},"select * where Col1<>''"))
That would be really great to know about! I am not at the level of writing scripts in Google Sheets yet.
Best of luck!