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.
Best Answer
Both parameters of
importrange
are strings. Strings can include values from other cells, by way of concatenation. For example,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".