Search code examples
google-sheetsgoogle-sheets-formulaimportrange

Use text from cell with IMPORTRANGE

I have two Google Sheets: the first contains data per week and the second gives an overview of that data. The sheets in the first are named by week. For example: Week 1, Week 2, Week 3, Week 4 ... The sheets in the second contain one cell which has the same text as the sheet names of the first document. This cell is A1.

Using the IMPORTRANGE function I want to show some data from the first (data) document in the second (overview) document. Currently the IMPORTRANGE formula looks like this:

=IMPORTRANGE("https://docs.google.com/...; "Week 1!C2:C5")

As you can see I have to change the sheet name I reference to manually. I want it to change automatically using the text in cell A1. So it should look like this:

=IMPORTRANGE("https://docs.google.com/...; "A1!C2:C5")

Is it possible to do it like this or do I need a script and how can I make it work?

Solution

  • Thank you for your tip about using ranges, I will use it in the future. You suggested the following formula:

    =IMPORTRANGE("https://docs.google.com/...; A1)

    It didn't work. I got it to work with the following formula:

    =IMPORTRANGE("https://docs.google.com/..."; (A1&"!C2:C5"))