Google Sheets – How to Use IMPORTRANGE with a Variable Range-String

formulasgoogle sheetsimportrange

I'm trying to create a user friendly sheet where data is pulled from a different Google Sheets based on input from the user. So far I have this working:

=IMPORTRANGE(C40,"2016 Data!W2:W13")

Where the user inputs the spreadsheet_key into C40 (i.e. – https://docs.google.com/spreadsheets/d/abcdef1234)

My problem is that I would also like a variable range_string (i.e. – 2016 Data!W2:W13) where the column name, i.e. – W, is the variable that the user adjusts. I've been able to get this far where user inputs W into cell B41 with this function:

="""2016 Data!"&B41&"2:"&B41&"13"""

to generate:

"2016 Data!W2:W13"

in cell C41. When I reference this cell in the IMPORTRANGE function, like this

=IMPORTRANGE(C40,C41)

I get this error:

#REF! – Cannot find range or sheet for imported range.

How can I get IMPORTRANGE to recognize the range_string from cell C41?

Best Answer

If your referring to the cell where the regular text is you don't need to add the additional "" around the ="""2016 Data!"&B41&"2:"&B41&"13"""

just simply enter: ="2016 Data!"&B41&"2:"&B41&"13"

You only add the additional quotes when your directly entering that data into the formula, not when its via the cell reference

Related Topic