Search code examples
google-sheetsimportconcatenationgoogle-sheets-formulatextjoin

Google Sheets IMPORTRANGE with cell variables

I have a google sheet where I import data from another google sheet like this:

=IMPORTRANGE("FILENAME", "SHEET1!D166:D180")

I have multiple formulas like this in the file that reference different columns in the "FILENAME" sheet. All of the data has a common file, sheet, and range of rows.

=IMPORTRANGE("FILENAME", "SHEET1!H166:H180")
=IMPORTRANGE("FILENAME", "SHEET1!J166:J180")
=IMPORTRANGE("FILENAME", "SHEET1!F166:F180")
=IMPORTRANGE("FILENAME", "SHEET1!I166:K180")

I make duplicate sheets and need to bring in a different range of data. Like the next sheet would need to use data for rows 181 to 195 instead of the 166 to 180. Is there a way set up 2 cells were I can put the range of data I want to pull from like cell R1=166 and R2=180 so that when I duplicate the sheet I can just update the R1 & R2 data cells to change the range of data without having to modify each formula? OR Is there a way to do this with at least only having to change the value of 1 formula instead of 5 different ones?

Solution

  • try:

    =IMPORTRANGE("FILENAME", "SHEET1!D"&R1&":D"&R2&"")

    you can also use CTRL+H

    enter image description here