INDIRECT for dynamic inputs for IMPORTRANGE in Google Sheets?

Question

I'm trying to aggregate data across several different Google sheets using IMPORTRANGE (or a similar function). The challenge I'm having is I need to reference a dynamic sheet name.

I'm importing an array from one sheet in a different spreadsheet using

=IMPORTRANGE("spreadsheetURL", "SheetName!A1:G22") 

But I need to do this for several different SheetName's. I'd like to have a cell (ex: Z3) that contains SheetName and reference that cell with something like

=IMPORTRANGE("spreadsheetURL", indirect(Z3)&"!A1:G22") 

but that syntax doesn't work.

Is there a way to do this?

Answer 1

If Z3 has the sheet name, instead of

=IMPORTRANGE("spreadsheetURL", indirect(Z3)&"!A1:G22") 

use

=IMPORTRANGE("spreadsheetURL", Z3&"!A1:G22")