Infolinks

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 Update spreadsheet cell value linking to another worksheet file, click the Microsoft Office Button Logo, and then click Excel Options.
Update spreadsheet cell value linking to another worksheet file

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.
Update spreadsheet cell value linking to another worksheet file

3. Still on Excel Options, click Advanced, and then go to Editing options section, then from there, enable/check Enable AutoComplete for cell values.
Update spreadsheet cell value linking to another worksheet file

4. Still on Advanced, go to When calculating this workbook section, check/enable Update links to other documents and Save external link values.
Update spreadsheet cell value linking to another worksheet file
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, Update spreadsheet cell value linking to another worksheet filethen click Trust Center Settings.
Update spreadsheet cell value linking to another worksheet file

From the Trust Center Settings, click on External Content Update spreadsheet cell value linking to another worksheet file. 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).
Update spreadsheet cell value linking to another worksheet file
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.
Update spreadsheet cell value linking to another worksheet file

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)
Update spreadsheet cell value linking to another worksheet file

I open the other workbook file (office-supplies.xls), and then browse to 2-4-2012 sheet.

Update spreadsheet cell value linking to another worksheet file

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.
Update spreadsheet cell value linking to another worksheet file

The whole formula on my office-supplies-total.xls, looks like this:
='[office-supplies.xls]2-4-2012′!$D$9

Update spreadsheet cell value linking to another worksheet file

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:

Related Posts Plugin for WordPress, Blogger...
Share

Leave a Reply

Your email address will not be published. Required fields are marked *

Infolinks