1 / 11

Excel 2007 Advanced

Multiple WORKBOOKS. Excel 2007 Advanced. Sharing data between multiple workbooks. June 21, 2012. Linking Workbooks. A link is a connection between files that allows data to be transferred (Shared) from one file to the other.

chaim
Télécharger la présentation

Excel 2007 Advanced

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Multiple WORKBOOKS Excel 2007Advanced Sharing data between multiple workbooks June 21, 2012

  2. Linking Workbooks • A link is a connection between files that allows data to be transferred (Shared) from one file to the other. • When two files are linked, the source file is the workbook that contains the data, and the destination file (sometimes referred to as the dependent file) is the workbook that receives the data.

  3. Linking Workbooks • To use information from a source file, an external reference must be used in a formula or function. • Syntax: • [WorkbookName]WorksheetName!CellRange • Use Single Quotes around the Workbook and Worksheet names if they contain spaces.

  4. Linking Workbooks • If the source file is located in a different directory than the destination file, the full file path name must be included with the single quote: ‘ • Example: • ‘C:\Data Files\[Detail Data.xlsx]Details’!A1

  5. Why Link Workbooks • A worksheet has become too large and/or is hard to use • You are creating or using a summary worksheet that consolidates data from several different files • Use a source workbook (file) for several other reporting workbooks • Your worksheet references source files that are continually updated

  6. Arranging Workbooks • Open all the files you plan to use. • In the Window group on the View tab, click the Arrange All button • Select the desired option for arranging the workbook: Tiled, Horizontal, Vertical, or Cascade • When arranging multiple workbooks, uncheck the Windows of active workbook option • Used when arranging worksheets within one workbook • Click the OK button

  7. Exercise • Arranging multiple workbooks • Creating external references • Editing absolute references

  8. Managing Linking Workbooks • Moving or renaming a source file will break the link to the destination workbook • Can repair using “Change Source” button • Go to Data tab – Connections group • Click on EditLinksbutton • Click on ChangeSource • Navigate to new source file

  9. Opening Destination Workbooks with Source Workbooks Closed

  10. Managing Linking Workbooks • Replacing a source file with an updated copy of the same name does not require re-linking • Changing the name of the destination file does not affect the links • You must have access to both source and destination files for linking to work

  11. Updating Linked Workbooks • Source and Destination files are open • Any change in the source file is automatically reflected in the destination file • Source file is open and the Destination file is closed • You can choose whether to update to the current values or continue to display the older values from the source file when the destination file is opened • Excel defaults to preventing links from updating, even if the link is set to automatically update

More Related