How to Reference Another File on Google Sheets
In most cases, your spreadsheet will relate to data and other items that are not part of that particular sheet. So instead of copying data or typing various instructions on how to get said information, there should be a quicker way to reference another file or piece of data.
Google Sheets has several functions that enable you to reference or draw data from different locations – a different sheet on the same spreadsheet, a different spreadsheet altogether, or even CSV files, links, and HTML pages.
This article will briefly explain the requisite functions.
Reference Data from Other Sheets in a Spreadsheet
If you work on a spreadsheet with multiple sheets, you can reference data from other sheets by using a simple function. Follow these steps:
- Open your spreadsheet.
- Select a cell.
- Type the following function: = [sheet name], exclamation point, a cell that you want to copy.
For example: =SheetA!C3, or =’Income sheet’!B1
You need to type the quotation marks around the sheet name if that name contains spaces or other symbols besides numbers and letters – as presented in the second example.
Import Data from Other Spreadsheets Using the IMPORTRANGE Function
The IMPORTRANGE function is different from the aforementioned function because it will reference a range of cells from a particular spreadsheet.
This function includes the link to the spreadsheet from which you’ll reference the data and the range of cells that will be copied to your spreadsheet.
Use the steps 1-2 from the previous section to start typing the function.Then type: =IMPORTRANGE [spreadsheet_url], [range string]).
For example =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/examplesheet1234”,“Sheet2!B5:C5”)
You need to place both the link to your spreadsheet as well as the cell range in quotation marks to make sure your data will be properly copied to your sheet. Typing the sheet name before the string isn’t necessary if you’re referencing the first sheet on the spreadsheet. Without the sheet name, the IMPORTRANGE function will automatically copy all the referenced cells from the first sheet.
Note: you’ll need permission for all the spreadsheets that you want to reference using the IMPORTRANGE function. If you reference the data from another spreadsheet for the first time, the user of the sheet will need to allow permission to pull data from it. Once the permission is granted, all editors of the second spreadsheet will be able to reference data from that spreadsheet.
In addition, if you try to reference too much data you may get an error message.
Codes for Importing Other Data
The IMPORTRANGE function is not the only way to import data. Here are some other referencing options:
- =IMPORTXML([link], [xpath_quary]): This will import data from any structured data format such as XML, HTML, TSV, CSV, ATOM XML and RSS feeds.
- =IMPORTHTML(url, query, index) will copy a list or a table from an HTML page.
- =IMPORTFEED(url, [query], [headers], [num_items]) will add a RSS or ATOM feed.
- =IMPORTDATA(url) is the best way to import a CSV or TSV file from a single link. You can have a maximum of 50 IMPORTDATA commands in one spreadsheet.
- =HYPERLINK(url, [link_name]) is the way to reference any web page on the internet. You can reference several link types such as http://, https://, ftp://, gopher://, news://, and others. If you use a protocol that isn’t supported by Sheets, your link label will be displayed but there won’t be a link attached.
These five functions are the only way that you can reference files that are outside of Google Sheets. For example, if you want to reference a file to download, you can use a ‘HYPERLINK’ function to attach a shareable Cloud link to the file in question. Other users can use the link to download it.
Reference Files with Ease
Thanks to these functions, you can seamlessly use external data and files to make your spreadsheet organized and easy to maneuver.
However, there are circumstances in which referencing other files can become inconvenient. For example, if you try to reference too much data, you can cause an error or current data loss. Make sure that you and your editors are aware of each item they are referencing, so your Spreadsheet remains neat.
How do you reference your files? Which function do you often use? Share your thoughts with the TechJunkie community in the comments section below.