How to link and automatically update a cell value of a spreadsheet file from another worksheet file on Microsoft Excel (2007/2010) Workbook
by: rjdreyes – R.Joseph D. Reyes
Linking value and get automatically update is easy within same worksheet or spreadsheet file, but if you want to get a value of a certain cell from another worksheet file, that’s something you thinking twice to make.
But don’t worry, here’s a simple task on how to link or get a value of a certain cell from another or external worksheet file and automatically update it, without a glitch.
If you are using Microsoft Office Suite 2007 or 2010, then luckily, you have this option.
1. At MS Excel 2007 , click the Microsoft Office Button Logo, and then click Excel Options.
At MS Excel 2010, click File, and then click Options.
2. From the Excel Options, click Formulas, and then from there, go to Calculation options section, then from Workbook calculation, and click Automatic.
3. Still on Excel Options, click Advanced, and then go to Editing options section, then from there, enable/check Enable AutoComplete for cell values.
4. Still on Advanced, go to When calculating this workbook section, check/enable Update links to other documents and Save external link values.
Also, on the when calculating this workbook, choose an appropriate workbook you are referring for the link values, be sure that the other workbook file is currently opened, so that can choose it on the drop down menu.
5. Still on Excel Options, click Trust Center, then click Trust Center Settings.
From the Trust Center Settings, click on External Content . From there, on the Security settings for Data Connections section, click on Enable all Data Connections (not recommended, but for the sake of automatic update of the workbook links/external link values, we must enable this).
At Security settings for Workbook Links, click on Enable automatic update for all Workbook Links (not recommended, but for the sake of automatic update of the workbook links/external link values, we must enable this).
(Why not recommended? Because there are certain scripts / external links or macros that can be harmful to the system, but anyway, you wouldn’t link any external links / scripts or unknown sources that can be harmful to your system right?)
6. Here’s my example on how to link a value of a workbook file to another workbook file for updated cell value.
Here’s my office supplies total expense – office-supplies-total.xls (I need to get the value from the other workbook file and dated 02-04-2012 worksheet)
I open the other workbook file (office-supplies.xls), and then browse to 2-4-2012 sheet.
Go back to office-supplies-total.xls, from the selected cell, type =
Go to office-supplies.xls and click a certain cell that you would like to get or link the value.
The whole formula on my office-supplies-total.xls, looks like this:
='[office-supplies.xls]2-4-2012′!$D$9
Now you already got an idea on how to link the external value from the other workbook file.
Feel free to comment here if you found one interesting to share or your own experience. That’s all! I hope this guide and tips helps you out! Cheers! 😀
(Note: Microsoft (Windows and MS Office Suite – Excel) Logo/Images/Pictures has a respective copyright. I used it for demonstration purpose only.)
Related Entries / Links / Sources / Articles / Sources:
Disable or Enable Circular Reference Warning on Microsoft Excel
BUY MICROSOFT OFFICE 2007, 2010 AT AMAZON US: