How to Make References to Other Files in Excel with Formula

When calculating data in Excel, you may often find yourself in a situation when you need to pull data from another worksheet or even from a different Excel file. Can you do that? Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.

External reference in Excel is a reference to a cell or a range of cells outside the current worksheet. The main benefit of using an Excel external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.

Simple Methods to Reference Cells in Other Sheets

1. Using "= reference a cell

Step 1 Click the cell in which you want to enter the formula.

Step 2 To reference cell A2, you can enter "=A2" in a different cell. This will display the value of cell A2 in the referenced cell.

Step 3 To reference a range of cells, such as A2 to A6, you can enter "=A2:A6" in a different cell. This will display the values of the specified range in the referenced cell.’

2. Using a cell reference across multiple sheets

Step 1 Choose the cell where you wish to enter the formula.

Step 2 Enter = (equal sign) in the formula bar

Step 3 Enter! followed by the cell reference of the cell to be pulled. Then press Enter

The value from the other sheet will now be shown in the cell.

Advanced Techniques for Pulling Data from Another Sheet

VLOOKUP Function: Definition and Use Cases

Definition:

The VLOOKUP function in Excel is a powerful tool for searching and retrieving specific information from a table or range of data. It stands for "vertical lookup" and is primarily used to find a value in the leftmost column of a table and return a corresponding value from a specified column.

Use Cases:

In "Sheet 1," we have a table with a "Code " column, and in "Sheet 2," we have the corresponding "Product ID" column. The goal is to populate the "ID" column in "Sheet 1" with data from "Sheet 2" based on the matching "Code."

Our aim is to copy data from the second worksheet's "ID" column and paste it into the first worksheet's "ID" column. This will be done for each individual Product.

To retrieve values from another worksheet, use the following steps:

Cell C2 should be selected and clicked.

Insert the following formula: =VLOOKUP(B2,Sheet 2!$D$2:$E$6,2,0)

By clicking and dragging the small "+" button at the bottom-right of the cell, you may move the formula down to the other cells in the column.

INDEX and MATCH Functions: Advantages over VLOOKUP

The INDEX and MATCH functions offer several advantages over the VLOOKUP function in Excel. Here are some key benefits:

• Flexibility in retrieving data in both vertical and horizontal directions

• Ability to use multiple lookup criteria for more precise data retrieval

• Non-sequential lookup capability, not limited to leftmost column

• Effective handling of dynamic ranges, adjusting formulas automatically

• Better error handling, providing alternative results or error messages when lookup value is not found

Example:

To refer to a cell or range of cells in another worksheet in the same workbook, use the worksheet name followed by an exclamation point before the cell address.

To refer to cell A1 in Sheet2, for example, type Sheet2!A1.

Using INDIRECT for Dynamic Sheet Referencing

INDIRECT attempts to analyze text as a worksheet reference. This enables the creation of formulae that concatenate a reference as text and utilize the resultant text as a valid reference.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

Referencing to Other Files Workbook in Excel

When creating external references to another workbook in Excel, there are two scenarios to consider: referencing to an open workbook and referencing a closed workbook.

External reference to an open workbook:

Format: [Workbook_name]Sheet_name!Cell_address

Example: =SUM([Sales.xlsx]Jan!B2:B5)

Usage: Use this format when the source workbook is open. Include the workbook name in square brackets, followed by the sheet name and the referenced cell or range of cells.

External reference to a closed workbook:

Format: Drive_path[Workbook_name]Sheet_name!Cell_address

Example: =SUM(D:\Reports[Sales.xlsx]Jan!B2:B5)

Usage: Use this format when the source workbook is closed. Specify the complete path to the workbook, including the drive letter and folder location.

Tips for Efficient Data Pulling in Excel

Keyboard Shortcuts to Ease the Process

• Copy selected cells: Ctrl+C - Copies selected cells to the clipboard.

• Cut selected cells: Ctrl+X - Cuts selected cells and places them on the clipboard.

• Paste copied or cut cells: Ctrl+V - Pastes copied or cut cells from the clipboard.

• Fill down: Ctrl+D - Copies content from the cell above and fills it down.

• Fill right: Ctrl+R - Copies content from the cell to the left and fills it right.

• AutoSum: Alt+= - Inserts the SUM function for the selected range.

• Flash Fill: Ctrl+E - Automatically fills data based on established pattern.

• Filter: Ctrl+Shift+L - Applies or removes filter from the selected range.

• Go to Special: Ctrl+G, then press S - Opens "Go To Special" dialog for selecting specific cell types.

• Sort: Alt+D, then press S - Opens Sort dialog to specify sorting options.

Frequently Asked Questions

Can I Pull Data from a Sheet in a Different Workbook?

Yes, you can pull data from a sheet in a different workbook by using external references or linking formulas. This allows you to access and retrieve data from another workbook seamlessly.

How Can I Combine Data from Multiple Sheets into One?

You can combine data from multiple sheets into one by using consolidation techniques such as using the Consolidate feature, writing formulas to reference data from different sheets, or using Power Query to merge and transform data.

What Is the Role of the INDIRECT Function in Pulling Data from Another Sheet?

INDIRECT function is a tool that enables you to maintain a fixed reference to a specific cell, cell range, or cell from another sheet. It ensures that the references remain unchanged, even if you modify the structure of your spreadsheet by adding or deleting rows or columns. This provides greater flexibility and accuracy when working with dynamic data and formulas in Excel, allowing you to confidently retrieve and manipulate information without worrying about reference changes

What's the Difference Between the VLOOKUP, INDEX, and MATCH Functions in Excel?

VLOOKUP:

• Retrieves a value based on a specific criteria.

• Searches for the value in the leftmost column of a table.

• Provides a simple lookup solution.

INDEX:

• Retrieves a value based on specified row and column numbers.

• Allows for more flexible data retrieval from anywhere within a table.

MATCH:

• Finds the position of a value within a range or column.

• Useful for determining the relative position of a value in a dataset.

Summary

In summary, this guide provides step-by-step instructions on how to efficiently pull data from another sheet in Excel. By utilizing functions like VLOOKUP, INDEX-MATCH, and INDIRECT, users can retrieve data accurately and save time. The article also covers techniques for referencing cells within and across multiple sheets, along with helpful keyboard shortcuts for faster data pulling.

With the use of WPS Office, users can seamlessly apply these methods and enhance their data management experience. Say goodbye to manual copying and pasting and embrace the efficiency of WPS Office for streamlined data retrieval.