Alok Paul
We mostly work with data in Excel. When calculating data in Excel, we often find situations where we need to pull data from one worksheet to another worksheet in the same Excel file. Actually, we can use the reference to another sheet in Excel easily. Here, we explained those methods.
Furthermore, for conducting the session, we will use Microsoft 365 version.
Download Practice Workbook
Download this practice sheet to exercise while you are reading this article.
5 Suitable Methods to Reference to Another Sheet in Excel
Here, we introduce a data set on the month of January of Prices of Fruits in a sheet named Jan Price. Now, we will refer to this sheet with another sheet. Here, Jan Price is our source sheet.
1. Directly Referencing from One Sheet to Another Sheet
Here, we can refer directly from one sheet to another sheet in Excel. Furthermore, we can create formulas that will reference a cell in a different worksheet from the sheet you are working on.
📌 Steps:
- Firstly, select the cell where the formula should go. Here, we are going to use the sheet named Reference Sheet and select Cell B5.
- Secondly, press the equal sign (=).
- Thirdly, click on the source sheet (Jan Price).
Here, we will see the formula on the formula bar.
- Now select the cell we want to refer to data. Here we will select Cell B5.
After that, we will see that the formula bar is updated.
- Subsequently, press ENTER.
- Finally, we will see that we are on our target sheet with desired data.
If the source data sheet is named Jan, it will be:
=Jan!B5As our source sheet name contains spaces, then the reference to the sheet will appear in single quotes.
='Jan Price'!B5If you change the value in the source sheet, then the value of this cell will also change.
- Then, you can now drag the Fill Handle icon horizontally to cells B5 and C5 to reference the values in the corresponding cells in the source worksheet.
- Lastly, again drag the Fill Handle icon to paste the used formula respectively to the other cells of the columns.
Finally, you will see all the values from the Jan Price sheet.
Read More: Excel VBA: Cell Reference in Another Sheet (4 Methods)
2. Reference to Another Sheet Using an Array Formula
When we need to refer to a range of data at a glance then we will use an array formula. So, in this section, we will use the array formula along with referencing sheet.
📌 Steps:
- First, select a cell in our target sheet Reference Sheet- Array.
- Then, press the equal sign (=).
- After that, click on the source sheet (Jan Price).
Also, we will see the formula on the formula bar.
- Now, select the cells we want to refer to. Here, we will select cells B4 to C8.
- Then, press ENTER, and we will refer our data to the target sheet.
Read More: How to Use Cell References in Excel Formula (All Possible Ways)
Similar Readings
- Mixed Cell Reference in Excel (4 Examples)
- Cell Reference in Excel VBA (8 Examples)
- Difference Between Absolute and Relative Reference in Excel
- Different Types of Cell References in Excel (With Examples)
- How to Reference Text in Another Cell in Excel (14 Ways)
3. Employing Define Name Feature to Refer to Another Worksheet
This method is ideal when referencing a cell/range from a different worksheet within the same Excel workbook. Moreover, it requires creating a name in the source sheet. After that, we can use that name to link the source sheet to our target sheet. Suppose we want to find out the total prices for January.
📌 Steps:
- First, select the range from the source data.
- Then, from the Top Ribbon >> go to the Formulas bar >> click on Defined Names and see a drop-down.
- Then, from the drop-down >> we will get Define Name and a new drop-down will appear.
- Lastly, from the last drop-down select Define Name.
As a result, we will get a Pop-Up named New Name.
- Then, on the Name box put a name that will be our reference name in the future. Here, we put Price as the name.
- After that, press OK.
Here, the names must be unique.
- Then go to your target sheet and use the SUM function.
- In the SUM function when you write “Pr” then you will find some options. So, choose Price.
- Actually, the formula is,
=SUM(Price)- After pressing ENTER we will get the sum of the selected range.
4. Use of INDIRECT Function for Calling Another Sheet in Excel
You can apply the INDIRECT function to use a reference of another sheet in Excel. Now, follow the steps given below.
📌 Steps:
- Firstly, you must write the sheet name in a cell. Here, we have written that in a new cell C10.
- Now, you must select another cell B5 where you want to keep the Fruits.
- Secondly, you should use the formula given below in the B5 cell.
=INDIRECT("'" & $C$10& "'!B5")- Thirdly, press ENTER to get the output.
Formula Breakdown
- First, $C$10 returns the value of the C10 cell. Which is Jan Price. Actually, the Dollar sign will freeze the C10 position for other cells too.
- Here, the Inverted Comma is required for using any text or sign.
- Lastly, the Ampersand sign (&) will join all these terms.
- Thurs, INDIRECT(“‘” & $C$10& “‘!B5”)—> becomes
- INDIRECT(‘Jan Price’!B5). Which will return the value of B5 from the Jan Price sheet.
- Similarly, write the corresponding formula in the C5 cell.
=INDIRECT("'" & $C$10& "'!C5")- Then, press ENTER.
Here, if you notice then you will see that we have to change manually the corresponding cell containing Fruit or Price. But the sheet name is fixed for all the cells. Thus, we used the Dollar sign ($) here.
- So, write the same formula changing the cell reference for the other cells.
Finally, you will get all the cell values.
5. Combine INDIRECT, ADDRESS, ROW & COLUMN Functions
Here, you can use a combination of some Excel functions for referencing another sheet in Excel. Basically, we will use the INDIRECT, ADDRESS, ROW, and COLUMN functions. So, let’s see the steps.
📌 Steps:
- Firstly, you must write the sheet name in a cell. Here, we have written that in a new cell C10.
- Secondly, you should use the formula given below in a blank cell B5.
=INDIRECT("'"&$C$10&"'!" &ADDRESS(ROW(B5),COLUMN(B5)))- Thirdly, press ENTER to get the result.
Formula Breakdown
- ROW(B5)→ returns the row number of the cell B5.
- Output→ 5.
- COLUMN(B5)→ returns the column number of the cell B5.
- Output→ 2.
- ADDRESS(ROW(B5),COLUMN(B5)) becomes ADDRESS(5,2).
- Output→ $B$5.
- Lastly, the Ampersand sign (&) will join all these terms.
- INDIRECT(“‘”&$C$10&”‘!” &ADDRESS(ROW(B5),COLUMN(B5))) becomes INDIRECT(“‘”&$C$10&”‘!” &$B$5)→INDIRECT(‘JanPrice’!$B$5).
- Output→ Papaya.
- Then, you can now drag the Fill Handle icon horizontally to cells B5 and C5.
- Lastly, again drag the Fill Handle icon to paste the used formula respectively to the other cells of the columns.
Read More: How to Use Reference of Worksheets in Excel (5 Examples)
Practice Section
Now, you can practice the explained method by yourself.
Conclusion
We discussed in detail the five methods to reference another sheet in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Further Readings
- Excel VBA Examples with Cell Reference by Row and Column Number
- How to Use Sheet Name in Dynamic Formula in Excel
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
- How to Use Cell Value as Worksheet Name in Formula Reference in Excel
- Excel VBA: Get Cell Value from Another Workbook without Opening
- Relative and Absolute Cell Address in the Spreadsheet
