We use Excel basically to ease our workload and save work time. For this, sometimes we refer to another cell in an Excel sheet or another sheet of an Excel workbook. We create references so that we don’t have to assign the data one more time. And sometimes we may also need to create a reference between our current workbook and another workbook in Excel. In this article, we will show you how to create reference with another workbook in Excel.
How to Create Reference with Another Workbook in Excel: 5 Ways
Let’s say, we have got a source workbook and in it, we have a dataset of different salesmen of an organization and their respective sales in three consecutive months and the total sales of these three months.
We want to create a reference between this source workbook and our new working book. For this, we will refer to the Total Sales range of the Source workbook in our current workbook.
In this section, you will find 5 suitable and effective ways to reference another workbook in Excel. We will demonstrate them one by one here. Let’s check them now!
1. Using Paste Link Option to Refer Another Workbook
We will create a reference between our source workbook and our current workbook. We will use the Paste Link option in order to serve our purpose. Let’s follow the steps below to refer to a different workbook.
Steps:
- First of all, copy the range of the source workbook (i.e. Total Sales).
- Now, get back to the new workbook and right-click on the mouse> click the Paste Link option.
- Hence, the command will create a reference to the previous worksheet.
Read More: Reference from Another Excel Workbook Without Opening
2. Create Reference with Another Workbook by Switching Between Them
We will now refer to another workbook by switching between the workbooks. In order to do so, follow the steps below.
Steps:
- Firstly, select a cell on your current workbook and put a “=” sign on it.
- Then, switch to the source workbook and click on the cell you want to refer (i.e. F5).
- Now, get back to the current workbook and you will see that it is referring to the source workbook.
- After that, press ENTER, and the reference with the cell of the source workbook will be created.
Note: This method will create an absolute reference to the cell of the other workbook.
- Here, remove the dollar sign to be unable Absolute reference.
- After that, drag the Fill Handle tool to Autofill the formula down to the next cells.
- Hence, your current workbook will create a reference with the source workbook.
3. Type Formula Manually to Create Reference
In this method, we will type the formula in the current workbook manually to create a reference with another workbook. In order to do so, just proceed with the steps below.
Steps:
- First, select a cell in the current workbook and type the formula below.
='[Source Workbook.xlsx]Data'!F5
Here,
- F5= the cell of the Source workbook
- Then, press ENTER, and the cell of the current book will refer to the cell of the source workbook.
- Now, drag the formula down for the next cells to refer to the source data.
Read More: How to Link Two Workbooks in Excel
4. Refer Another Workbook with Named Range
We will now create a reference with another workbook by using the named range. So, let’s start the process like the below.
Steps:
- First of all, go to the Source workbook that you want to refer.
- Now, select the range you want to refer> go to the Formulas tab> select Define Name in the Defined Names group> click Define Name.
- Then, the New Names dialogue box will appear. Assign a name (i.e. Total_sales) in the Name field and click OK.
- Then, get back to the new workbook and apply the following formula to a selected cell.
='[Source Workbook.xlsx]Data'!F5
Here,
- F5= the first cell of the Source workbook
- Now, hit ENTER, and the range that you assigned name will appear here.
In this way, you can create a reference to another workbook.
5. Using Consolidate Tool
You can also create a reference to another workbook by using the Consolidate tool. For the same set of data, we want to sum the total sales of the source workbook in the new workbook. if you want to apply this tool to refer to a new workbook, proceed with the steps below.
Steps:
- Firstly, select a cell of your current workbook> go to the Data tab> click Consolidate from the Data Tools group.
- Now, Consolidate dialogue box will appear. Assign the first range in the Reference field> click Add.
- Then, click the right arrow of the Reference field to add another reference range> click Add.
- Repeat the same process for the as mane reference as you want to add and then click OK.
- As a result, your current workbook will create a reference with the source workbook.
Read More: Link Excel Workbooks for Automatic Update
Things to Remember
- Make sure that Calculation Options is set to Automatic, otherwise, the formulas may not work properly.
- Keep both the workbook open during the process.
- You can refer to multiple workbooks by following the methods.
- Don’t forget to put the ‘ ’ sign when you type the formula manually.
Download Practice Workbook
You can download the practice book from the link below.
Conclusion
In this article, we have tried to show you some methods of how to create reference with another workbook in Excel. Hope this article has shed some light on your way to create reference with a different workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly check our website. Have a great day!
