Cross Importing Google Sheets

Need to think outside the workbook? Try connecting two workbooks together with a two-way relationship to logically organize your data and calculations.

I've been noodling with this interesting technique lately that you might get a kick out of. I'm calling it cross importing spreadsheets. The idea came from how I manage my finances, which is a constellation of workbooks in Google Sheets. Basically, you can connect two workbooks together via filtered imports!

My typical workbook setup showing one core workbook connected to several child workbooks

Remember that a Google Spreadsheet is a workbook, which has a collection of sheets.

The core workbook has both the "low level view" like individual transactions as well as the "high level view" like aggregate values of overall cashflow month to month. This works well because it lets you quickly see what's going on at the high level and then drill down into the data to answer questions all in the same workbook.

This falls apart, however, if you have a lot going on and need lots of tabs. Already, I'm looking at tabs for:

Checking and cash accounts,
Brokerage accounts,
401(k) and retirement,
Cryptocurrency,
Real estate, and
Startups.

That means that core workbook has many many tabs, even before breaking things down into individual assets (like investment property) or even individual projects (like renovations or getting married).

If you haven't already, take a look at my post on using Mint to manage your finances across accounts. It makes downloading data in bulk very easy for use in this technique.

The crux of this technique is the IMPORTRANGE function in Google Sheets. It works just like it sounds! You give the URL for the spreadsheet you want to import data from, and specifiy the range of data you need brought in. The function will overwrite any nearby data with the array you specified.

Remember: You need to connect the spreadsheets before creating more complex functions, or you'll get an inscrutible formula error. e.g., Try using =IMPORTRANGE('<sheet>', '<range>') before trying to use =SUM(IMPORTRANGE('<sheet>', '<range>')).

This is already quite powerful going one-way! You can set up master spreadsheets to export useful collections of data like transactions, daily business activity, or reusable static data (like life-expectancy tables). It even works with named ranges and arrays to help you get exactly what you need.

But with a two-way connection, you can subdivide data sets among many consumers and then re-import calculated metrics for use in a dashboard view.

Core workbook exports bulk transactions for filtering while individual workbook exports final ratios and values.

It's a simple technique that can pay off big in the long-run. Importing/exporting live data can save you time and avoid duplicating data across several sheets. It's a great way to keep complex things synchronized!