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?