Referencing Data from Different Sheets or Workbooks
This article explains how to reference data from different sheets or workbooks in Excel. Multiple methods are covered so you can choose the one that suits your needs.
Referencing Data from a Different Sheet
To reference data from a different sheet, you can use a direct cell reference.
Below is an example of data from Sheet2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Quantity | |
| 2 | Apple | 100 | 5 | |
| 3 | Banana | 150 | 8 |
Steps
- In the target cell, enter the reference formula using ! to indicate the sheet name:
- =Sheet2!B2
- Press Enter to fetch the data from cell B2 in Sheet2.
How It Works
This reference retrieves the value from cell B2 in the specified sheet (“Sheet2”).
Result
The formula =Sheet2!B2 retrieves the value 100 from cell B2 in Sheet2.
Referencing Data from a Different Workbook
To reference data from another workbook, you specify the workbook name in the formula.
Below is an example of data from “OtherWorkbook.xlsx”:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Quantity | |
| 2 | Apple | 100 | 5 | |
| 3 | Banana | 150 | 8 |
Steps
- Open the source Excel file.
- In the target workbook, enter the formula as follows:
- =[OtherWorkbook.xlsx]Sheet1!B2
- Press Enter to fetch the data from cell B2 in “OtherWorkbook.xlsx”.
How It Works
This formula retrieves data from cell B2 in the “Sheet1” sheet of the specified workbook (“OtherWorkbook.xlsx”).
Result
The formula =[OtherWorkbook.xlsx]Sheet1!B2 retrieves the desired value from the specified workbook.
Using the INDIRECT Function for Dynamic Referencing
The INDIRECT function allows you to dynamically reference sheet names or cell addresses based on cell values.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sheet Name | Item | Price | |
| 2 | Sheet2 | Apple | 100 |
Steps
- Enter the name of the sheet to reference in cell A2 (e.g., “Sheet2”).
- In the target cell, enter the following formula:
- =INDIRECT(A2 & “!B2”)
- Press Enter to dynamically reference the value in cell B2 of the specified sheet.
How It Works
The INDIRECT function dynamically constructs a reference based on the value in cell A2, allowing for flexible referencing.
Result
The formula =INDIRECT(A2 & “!B2”) retrieves the data from cell B2 in the sheet specified in cell A2.
Conclusion
Excel provides multiple methods to reference data from different sheets or workbooks, such as direct references, file references, and the INDIRECT function. These techniques make it easy to manage and retrieve data dynamically.