By Shivani Garg
External Reference in MS Excel
It may happen that you have data in one Excel workbook that you want to refer in a formula in another workbook. In such cases, Link can be created between the workbooks using an External Reference.
The benefit of using an external cell reference is that when the data in the outside workbook changes, MS Excel automatically updates the value returned by the External Cell Reference.
How to Create External Reference in MS Excel
Let's learn with Example
Below is the Example of workbooks of different Zones North & South workbook and company's Total Sales Record workbook.
>> Open all the workbooks (North & South and company's Total Sales) at the same time.
>> In Total Sales of Company workbook, select cell B3 and type the equal sign =
>> On View tab, Click Switch Windows.
>> Click North Sales Workbook and select cell B3.
>> On View tab, Click Switch Windows. Click South Sales Workbook and select cell B3.
>> Again, Click Switch Windows. Click Total Sales of Company Workbook.
Press Enter and you will get the Result
Remove All the $ sign before copying the Formula to other cells. Then only it will take the Relative Reference.
Read More Cell References in MS Excel
Copy and Paste or Drag the formula to other cells.
Repeat all the above steps for Product 2
Result
Close all workbooks. Change a value in the workbook of any Zone say (North Sales of January)
Now, when you will open the Total Sales of Company Workbook. An ALERT will show
Click Options. A dialog Box will Appear.
Click Enable this Content and Click OK
Once your Enable the Automatic Update , the Total Sales Record will Automatically get updated.
Read More on References :
Read More on Functions :
Also Read :
Labels:
MS Excel