How do Excel functions reference data from other worksheets and workbooks?

Key points of this article

To quote within the same workbook, directly write "Table name! Cell". If the table name contains spaces, enclose it in single quotes.
The search column of VLOOKUP must be a column on the left side of the range, and the returned column number is calculated from the left side of the range.
For exact matching, fill in FALSE for the fourth parameter. If omitted, the result will be returned as an approximate match.
External links depend on the file path and source file status. If the path changes or the calculation mode is manual, it will not be updated.
For external data that is only used once, copying it as a value is less troublesome than keeping the link.

When making summary tables in Excel, data are often scattered across several worksheets or even placed in another file. Many people search for "How to use Excel functions in another worksheet", and what really gets stuck is often two things: an error occurs as soon as the formula is written, or the numbers no longer change after switching to another disk or computer. This article is written for office workers who need to retrieve data across tables and files. It explains how to write references, the reasons for external link failure, and the troubleshooting sequence.

After reading this, you should be able to do three things: write correct cross-table references, determine why external links are not updated, and decide which data structure is more worry-free. The examples in the article can be reproduced by yourself, such as a monthly sales summary consisting of 3 tables.

How to reference data from another worksheet and file? One sentence conclusion

In the same workbook, write the worksheet name and exclamation mark in the formula, and then add the cell address, for example =Sheet2!A1. When referencing another file, Excel will record the full path of that file. Once the path changes or the source file is inaccessible, the results can easily end up at the old value or cause errors.

your situation	Recommended practices	reason
Data and summaries in different worksheets in the same file	Directly write "table name! cell" and use VLOOKUP or XLOOKUP to search	The path is fixed, does not rely on external files, and is more stable
The other file is the long-term updated source data	Keep external references, place both files in the same folder, and check links regularly	After the source file is updated, the summary can be refreshed, but the path must be managed.
Use another file only once	Open the source file, copy the required data, and paste it into the current file in numerical form	There are no residual links, so it is not easy to report errors when sending to others.
Data is placed on a network shared disk or transferred between multiple computers	Use the full network path, or put the data into a shared file	The mapped drive letter may not exist on other people’s computers
The data was originally a PDF report	First identify and export to Excel through OCR, and then quote according to the above method.	The PDF itself cannot be directly referenced by formulas, and the numbers must be checked after identification.

Before you start: Confirm these points first

Confirm the table name and location of the source data. When the table name contains spaces, brackets or special symbols, the formula must be enclosed in single quotes, for example ='Sales Data'!A1. When the name is unclear, first change the source table to a short name without spaces, so that subsequent formulas are less prone to errors.
Confirm whether the search value will be repeated. VLOOKUP returns the first row of matching records found in the left column of the range. When the same number appears repeatedly, the result may not be the row you want. You need to remove the duplicates first or use more precise conditions.
Confirm whether source files will be moved or renamed. The external reference saves the complete path. If it is renamed or moved to another folder, the link will be broken.
Confirm who will open this file. Only use it on your own computer, and have completely different path requirements than sending it to colleagues or putting it on a network disk for sharing by multiple people.
Confirm calculation settings. If the calculation mode is set to manual, the formula will not be refreshed automatically, and the numbers will be updated after manual recalculation.

Steps to use VLOOKUP to reference another table in the same workbook

Select the cell where you want to display the results in the summary table, enter the equal sign and function name, for example =VLOOKUP.
The first parameter selects the search value in the current table, such as the employee number in A2.
The second parameter fills in the area of the source table in the format of "Table name! Starting cell: Ending cell", for example, Sheet2!$A$2:$D$200. The column to the left of the range must be a lookup column, otherwise VLOOKUP cannot look to the right.
The third parameter fills in the column number of the returned result in the area. This is the column from the left in the range, not the column in the worksheet. For example, the range starts from column A, and if you want to get the data in column C, fill in 3.
Fill in the fourth parameter with FALSE, indicating an exact match. When omitted, Excel defaults to approximate matching, which may return results that appear normal but are incorrect.
Press Enter to confirm, compare the results with the source table, and then fill in the formula downwards. Before dragging to fill, the area address must be added with a $ symbol to make it fixed and not offset with dragging.
If the source data is in another file, open the source file first, and then click the corresponding cell in the formula. Excel will automatically generate a reference with the file path. After saving and closing the source file, check that the summary table numbers are still correct.

Why can't data transferred from other disks be updated automatically?

The results of external references come from two parts: the file path of the formula record, and the last calculation result (cache value) saved in the file. When the source file is not opened, Excel may not be able to read the new numbers, so it displays cached values or prompts that it cannot be updated. Common reasons are as follows:

The path has changed. If the source file is moved, renamed, or the original drive letter changes, for example, after the D drive is replaced by the E drive, the formula still points to a non-existent file.
No update links allowed. When opening a file with external links, Excel usually pops up a security prompt. If updates are not allowed, the summary table retains the old values.
Calculation mode is manual. The formula will not be recalculated as the data changes and needs to be recalculated manually by pressing F9.
Source files are saved as other formats. For example, after saving as CSV, the original worksheet structure may change, and the original reference can no longer find the corresponding cell.

When encountering this kind of problem, first confirm the path, then allow the link to be updated, and finally check the calculation mode. It is more efficient to troubleshoot in this order.

Check each item after completion

After you get the results, don't just see whether the numbers appear, but also check whether they are reliable item by item.

Check items	How to see	What should you do if there's a problem?
Reference path	Click on the formula to see if it contains the correct file name and path	Respecifying the source file location in Link Management
Sampling controls	Randomly select 3 to 5 rows and verify them one by one with the source table	Check whether lookup values are duplicate and if there are any extra spaces
Return column number	Compare the region, and confirm the third parameter number as the column in the region	Adjust column numbers; do not apply the worksheet column numbers directly
Error value	Check if #N/A or #REF appear!	#N/A usually cannot find the lookup value; #REF! Indicates that the cited area or document is invalid
Calculation status	After modifying the source data, does the summary table change synchronously?	Change the calculation mode to automatic, or recalculate using F9
Testing by changing environments	Open it on another computer or after switching folders	If the link fails, change it to a value or place the file in the same folder

📝 Why do we sometimes change links to numeric values?

Pasting values severs the connection between formulas and source files, reducing file size and error risk, making them suitable for sending to others or archiving. The trade-off is that the source data changes later, and the summary table does not update with it; it needs to be duplicated again. The criteria for judgment are simple: if the data is only used once, or only one final result is needed, paste it as a value; If the source data is continuously updated, keep the reference and fix the two files in the same place.

❓ Frequently Asked Questions (FAQ)

1. What should I do if VLOOKUP returns #N/A?

First, confirm that the lookup value does indeed exist in the lookup column of the source table, then check whether the numbers on both sides are both text or numbers, and whether there are any spaces before or after. Inconsistent formatting is a common reason.

2. What happens if you transfer the source file to a new folder after cross-file reference?

External references record the complete path; if moved, the link will break. When opening the summary table, you can find the original file in Link Management and reassign its new location.

3. Why does the number remain unchanged after closing the source file?

Excel saves the result of the previous calculation as cached value. When the source file is closed, it cannot read new data, so it displays cache values. The number will only refresh after reopening the source file and allowing the update link.

4. Can you not use VLOOKUP?

Yes, you can. XLOOKUP is more straightforward and does not require the lookup column to be on the left; The INDEX and MATCH combinations can achieve the same effect, suitable for situations where you need to look left. Both methods rely on the same reference path, so the above troubleshooting order is equally applicable.

5. What if the source data updates after copying to a value?

Values do not automatically sync; you need to reopen the source file, copy the new data, and paste it into the summary table. If you do this often, it's recommended to keep references and organize file locations.

📝 Summary

How to use another worksheet in Excel functions depends on the reference syntax and file path: for the same file, use "TableName!" Cell", ensure the path across files is stable. If you encounter a situation where updates are not being updated, check the path, link permissions, and calculation mode in sequence, and usually find the cause.

Open OmniDoc and process your table file