IMPORTRANGE is what allows your Google Sheets files to talk to each other. Here are two tips to improve that talk.
IMPORTRANGE is a formula that allows you to import ranges (no way!!) of cells from one Sheets file into another. You update a value in your source file, and the update carries through to any and all other files to which that value is being imported. I use this formula every day. And I'll say this: if you are building Sheets-based models and tools for an organization, then you need to be a master at IMPORTRANGE.
Start with these two tips:
Create a tab where you house all of your import credentials
Isolate your imports to their own tabs
Here is my Import Ranges Template. I'll use this to show both of these tips in context. Feel free to make a copy and use on your own.
Tip #1. Create a tab where you house all of your import credentials
In my example (and in all my models) I label that tab "Ref - Imported Ranges". In this tab, there is a table with all of the details needed to import a range: the file ID (more info on that, if needed); the sheet name; and the cell range. There's also a couple descriptors I find helpful (the file name and a description). And lastly, perhaps most importantly, there's a "Test" column, which will produce an error if the import is not working.
It was a good day the day I came up with the "Ref - Imported Ranges" sheet. Here's why it's so helpful:
It's really helpful to know what files are linked to each other. Without something like this, it takes a lot of digging to figure that out
Imports sometimes break. This gives extremely clear visibility into that
Sometimes you have to change the file you're sourcing from (for example, we created a v2 of the financial model, so now we want to reference that, not v1). This makes that super easy
For everywhere else in the file that you want to actually import data, your formulas become extremely easy to write... speaking of which...
Tip #2. Isolate your imports to their own tabs (and use my formula)
When I import a range, I give that range its own tab and label it as "Import - ___" (and color it red). In my template, you can see two examples: "Import - Rev Table" and "Import - Tickets Table". For both, the entire tab is being populated by a single formula:
= IMPORTRANGE ( 'Ref - Import Ranges'!E4 , "'" & 'Ref - Import Ranges'!F4 & "'!" & 'Ref - Import Ranges'!G4 )
I could go on for a while about all the reasons I think this is the right approach, but the biggest point is - IMPORTRANGE is easily broken. By isolating it in its own tab, and coloring it red, and labeling it an import, people tend to know not to touch it (a good thing). At least that's my experience.
That's all I got. Thanks for reading!