2

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.

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.