Inserting text from a cell into an IMPORTRANGE formula

Question

I have a spreadsheet (Spreadsheet B) with a number of formulas which require data from another spreadsheet (Spreadsheet A). Many of the formulas in Spreadsheet B reference different sheets (on different tabs) within Spreadsheet A.

I want to know if it is possible to list the name of each sheet (tab) within Spreadsheet A into Spreadsheet B just once and then use that cell as the text reference for an IMPORTRANGE formula.

For example, three of the sheets (tabs) from Spreadsheet A are: Names, Address and Phone Number.

If I list these names in individual cells in Spreadsheet B (i.e. A1 - Names, A2 - Address etc), can I include those cell references in the IMPORTRANGE formula?

e.g. instead of importrange("Spreadsheet A Key","Names!B1") I want to have something that references the cells with text in like - importrange("Spreadsheet A Key","**Spreadsheet B** **A1**!B1")

I have tried INDIRECT with no success, and given that the cell reference I want included in the formula lies within the search parameters for Spreadsheet A, I don't even know if what I'm asking is even possible.

Answer 1

Both parameters of importrange are strings. Strings can include values from other cells, by way of concatenation. For example,

=importrange("Spreadsheet A Key", A1 & "!B1")

means that the name of the sheet to be imported will be taken from cell A1 of the current sheet.

The character & means concatenation: "Names" & "!B1" is "Names!B1".