Microsoft Office Tutorials and References
In Depth Information
Creating Links Between Workbooks and Worksheets
Creating Links Between Workbooks and Worksheets
Cell and range references can point to a location on the same worksheet, on a different
sheet, or in a different workbook. The most common use of links is to refer to a cell within
a formula, but you can also use links (formally known as external references ) as a way to
consolidate information from multiple workbooks into a single location. For example, if you
collect identically formatted workbooks from different departments, you can create a new
workbook, design a summary sheet, and ill the new sheet with links to the totals row from
each individual department’s sheet. When you update the data in the departmental
workbooks, your summary sheet is automatically updated in real time.
As a security precaution, Excel blocks automatic updates of links to external files. When you
open a workbook containing external links for the first time, you see the Security Warning
bar shown here.
After you click Enable Content, you’re given another choice via this dialog box, which
appears regardless of whether the external workbook is currently open or not:
If you click Update and the workbook that contains the linked data is available, Excel
refreshes the data on your worksheet automatically. (This is true even if the workbook file
is not open; Excel retrieves the data in the background.) Click Don’t Update if you know
that the linked workbook is unavailable (because you’re not connected to the network, for
To add an external reference, copy it from the original location, and then use the Paste Link
option (on the Paste Special menu) to insert it in the destination workbook. The syntax for
the external reference contains the name of the worksheet format, the file location, the
workbook name, and the tab name, with the last three elements separated by exclamation
points, as in this example:
=Excel.Sheet.12|'D:\Budget\Results 2010.xlsx'!'!Marketing_Detail!R44C2'
Search JabSto ::

Custom Search