How to use INDIRECT inside an IMPORTRANGE function?

Question

I need help to dynamically fetch a value from another Google spreadsheet like this: FILE1 has 0 or 1 values in column G. Now I want to fetch the G column value from FILE1 with a row number specified in B7 in FILE2.

I've learned that INDIRECT could solve the dynamic bit fetching from column G with B7 as row number like this:

=INDIRECT("G"&B7)

but I just can't make it work together with IMPORTRANGE:

=IMPORTRANGE("KEY";indirect("Sheet1!G")&B7)
=IMPORTRANGE("KEY", "Sheet1!G"; indirect(&B7))

Above examples just give me wrong interpretation of formula error. Is this possible to achieve with Google Sheets and if yes, how?

Answer 1

The second argument of importrange must be a string, not a cell reference. The command indirect takes a string and returns a cell reference, which is not what you need. Just use

=IMPORTRANGE("KEY"; "Sheet1!G"&B7) 

To clarify the difference:

• Strings: "B4", "Sheet1!A3", "Sheet1!A"&E2
• Cell references: B4, indirect("Sheet1!A3"), indirect("Sheet1!A"&E2)

Answer 2

Short answer

Instead of nesting a function like INDIRECT inside of IMPORTRANGE, put IMPORTRANGE inside of another function like FILTER or INDEX, among others.

For the case of the OP the formula could be the following

=INDEX(IMPORTRANGE(spreadsheet_key,"Sheet1!G:G"),B7)

Explanation

IMPORTRANGE requires two arguments:

• spreadsheet_key
• range_string

range_string should be a string that is a well constructed reference in A1 notation. It could be a cell or range reference but the value of the referred cell/range should be a string.

The examples provided by the OP, besides that INDIRECT doesn't work as he/she expects, failed to construct the string. The details of strings construction should be explained in a specific question, I think.

FILTER could return a single or multiple values and it requires at least two arguments:

• reference or array
• condition

INDEX return a single value and it requires at least two arguments:

• reference or array
• row, optional, default 1.
• column, optional, default 0)

Answer 3

For users that want to do the same thing, but swap out the sheet name (instead of the cell name) they can use the following formula.

=IMPORTRANGE("KEY", A4&"!E2:E")

...where A4 contains the name of the specific sheet you want to reference in your other spreadsheet and E2:E is the range that you want to import from that other spreadsheet.

Answer 4

How would we use IMPORTRANGE if we dont know the exact cell id but we want to get the data from another sheet from the cell with the same cell id ? It's clear using specific cell id but on copy/paste you have to correct it. I tried using as cell ID ADDRESS(row(),cell()) but I get #REF! error.