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:
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.