Use text from cell with IMPORTRANGE

0
votes

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?

2
google-sheetsgoogle-sheets-formulaimportrange
Are you willing to use a third-party tool? In this case I can write an answer using Import Sheet. – orschiro

2 Answers

0
votes

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"))
0
votes

sure, just try it:

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

I also recommemnd you use named ranges (google it). this allows you to just type in "week1" into cell A1, instead of something like "Sheet3!A1:B343." Without named ranges any complex spreadsheet will turn your formulas into an indecipherable mess.