Related to IMPORTRANGE

Question

Say I have two spreadsheets: name and grade. I have a lot of data in name that I want to pull some of into grade. I used the following:

=IMPORTRANGE("KEY_OF_name","Sheet1!E1")

Great! That would pull the data in cell E1 in name to grade but I want to make the formula more dynamic. With the same function I want to take the number "1" in "E1" from a cell in grade. So if the value of B2 in grade is "1", the formula for pulling data to grade is:

=IMPORTRANGE("KEY_OF_name","Sheet 1!E1")

and if the value of B2 in grade is "6", the formula is:

=IMPORTRANGE("KEY_OF_name","Sheet 1!E6")

I want to write down the range in the function as En where:

• E is the column in name
• n is a number in cell B2 in grade.

How would I do that?

Answer 1

Try,

=IMPORTRANGE("KEY_OF_name","Sheet 1!E"&B2)