How To Fill An Import Range Function With Dynamic References

This post originally appeared on the Coalmarch blog in 2015 (Coalmarch is a former employer of mine). They were going to remove it, but asked if I wanted to move the post to my site. So I did! The following is the original blog post, with one new section at the end if you want to get extra fancy.

———————————————————————————————

We use the “IMPORT RANGE” function in Google Sheets frequently. It’s crucial when trying to work with the behemoth of admin documents we have. Luckily, it’s a pretty straightforward function. All it does is pull in a cell from another sheet into the sheet we’re working in. But, there’s one problem with it: if you’re working with a big document and you want to fill the function down or across, it doesn’t work. Luckily, I found a solution!

First, in case you don’t use it, let me show you how it works.

The IMPORT RANGE Function

Google will tell you that the function looks like this:

=importrange(spreadsheet_key, range_string)

Here’s what that means:

spreadsheet_key: Every Google Sheet has a URL that looks something like this (“https://docs.google.com/spreadsheets/d/1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_…“). The spreadsheet key is that string of gibberish after the /d/. So, in the case above, 1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_axvWDVsY9h26p0.

range_string: This is just the cell from the target sheet that we’re looking to import into our working sheet. We’ll need to include the sheet name and the cell name. So, if we want to pull what’s in cell A1 from our target sheet called “New Document”, we’d just use the standard notation of New Document!A1.

Putting it together, this is what our function would look like (notice that both parts of the function have to be surrounded with double quotes):

=importrange(“1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_axvWDVsY9h26p0”, “New Document!A1”)

That’s it! So here’s an example. I have three documents I’m working on. Test Doc 1, 2, and 3. Doc 2 has client contact name information in it, Doc 3 has client location information in it, and I want Doc 1 to be a hub that has both contact name and location information in it.

We could manually copy and paste the info from 2 and 3, but if we change those frequently, we may want to have it dynamically pull that information. So, we use IMPORT RANGE.

So let’s grab the “Contact Name” info from Test Doc 2. To do so, I went to Test Doc 2 and grabbed the spreadsheet key from the URL, the sheet name, and note that I want to pull the first contact name, which is at cell B2.

So my formula in cell B2 of Test Doc 1 looks like this:

=importrange(“1vXc1-xuRjdUmakIO5jvuOe4qcbcASPF_axvWDVsY9h26p0”, “Contact Info!B2”)

Then, hit enter. The first time a spreadsheet tries to communicate with another spreadsheet, we’ll have to authorize access between them:

Once we do that, we should see the data from our other sheet!

So here’s the tricky part. Normally with a function, we’d be able to “fill” it down and it would change the function to match the new area. I’ll show you an example.

Let’s say instead of referencing a different sheet, the client names were in the same sheet but just in a different column and we were just referencing them there. In that case, the formula would just refer to the other column, or in this case, it would be “=D2”:

If we fill that down, it will fill the rows correctly:

Notice how the new formula changed from “=D2” in row 2 to “=D4” in row 4. But if we try to do that with the IMPORT RANGE function, nothing changes. It imports the same cell every time:

The reason is in the name of that part of the function: range string. A string is text–it’s not a row or column or number like a function normally has. A spreadsheet can tell what you’re doing when you try to fill a formula down that is referencing a cell–you probably want the formula to reference the cells below the original. If you’re filling it to the right, you probably want it to reference the cells to the right of the original.

If it’s a string, it can’t tell that you’re referencing a cell, it just sees the letters and numbers that make up “Contact Info!B2”.

So. How do we dynamically reference that cell so that it fills correctly?

The ADDRESS, COLUMN, and ROW Functions

The answer is a function called ADDRESS. It’s a pretty meta function. Google says it “returns a cell reference as a string” when you input the row and column. So say you tell it row 1, column 1, it will return “A1”, but it will return it as a string and not the actual cell. Sound familiar? That’s exactly what we need for the “range string” part of the IMPORT RANGE function to work.

Here are the full details of the address function. It looks like this:

=address(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

That means:

row: Simply, the row number you’re referencing.

column: The column you’re referencing. Keep in mind this is a number not a letter, so A would be 1, B would be 2, etc.

[absolute_relative_mode]: This is optional. You can tell the function whether you want it to use absolute or relative references. I leave this out.

[use_a1_notation]: Also optional. You can use A1 or standard (R1C1) notation. I leave this out as well.

[sheet]: Also optional, but this one is useful. You can tell it the name of the sheet you’re looking for in case there are multiple sheets in the doc you’re pointing to.

So if we want the B2 cell in Test Doc 1 to grab the B2 cell in Test Doc 2, we just need the range string in our IMPORT RANGE function to reference the current row and column. Luckily, there’s a simple way to do that using the ROW and COLUMN functions. If you use the formulas of just “=row()” and “=column()” with nothing in the parenthesis, you’ll get the row and column of the cell you’re currently in.

You can see this below. I added the ROW functions to cells D2, D3, D4, and D5 and it gave me the row numbers. I added the COLUMN functions to C6, D6, and E6 and it gave me the column numbers.

This means we can use these formulas within the ADDRESS function. The final formula would look like this:

address(row(),column(),,,”Contact Info”))

Which just means “give me the address of the cell in the “Contact Info” sheet of the same row and column of where I currently am.” Note that commas separate each part of the function, so because we’re skipping the first two optional parts, you have to put three commas in between the column and the sheet name.

So, to pull it all together, we can use this in our IMPORT RANGE:

=importrange(“1vXc1-xuRRvvDA5jvuOe4qcbcASPF_axvWDVsY9h26p0″,address(row(),column(),,,”Contact Info”))

We can now fill this down and it will work perfectly. The row() and column() functions change when we fill it down and it returns the range string the IMPORT RANGE function needs.

One last trick. The third column isn’t going to work like this because the column (C) doesn’t match up to where you want to grab the info from in Test Doc 3 (B). So, a simple edit of the IMPORT RANGE function is all it takes (the change is bolded):

=importrange(“1ONvf5LVFoDbPoFmNn07q8Qy9XteOC41zSqAYADNTOr8”,address(row(),column()-1,,,”Location Info”))

We’re referencing a different spreadsheet, so I updated the spreadsheet key and sheet name. But, the tricky part is that “column()” is now “column()-1”. The orginial “column()” would have returned a 3 (meaning C) but we want a 2 (meaning B), so a simple minus one gets us there.

The final doc:

That’s it! Our doc will automatically update and we’ll be able to fill the formulas down if we add new rows in the future.

If I’m missing anything or if you’ve tried this and it doesn’t work, email me and I’ll help you out!

Cheers!

</end of original post>

———————————————————————————————

New Stuff!

As I was porting this over, I realized two things:

1) Linking to the example docs may help some people, so here they are:

1. Test Doc 1
2. Test Doc 2
3. Test Doc 3

2) You can make this example a little faster if you had an especially large spreadsheet with many columns. So, in the “Test Doc 3”, the original setup is in the “Simpler” tab, but I added two “Fancier” tabs. The “Fancier (Reference)” tab is a table for VLOOKUPS and the “Fancier (Final)” tab is the original table using those VLOOKUPs.

The Fancier version just makes it so that you don’t have to write different formulas for each column–as long as you include the lookup data in the “Reference” tab, you can have as many columns as you want and you only need one formula.

Cheers!