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


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?

1 Answer 1


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.