Tuesday 6 November 2018

Microsoft Excel - Linking-Documents

Linking Documents

Worksheet files can be linked together dynamically using formulas; as the information in one worksheet changes so does the corresponding formulas in the other worksheet will update to reflect these changes. The formula is a pointer; the value in the cell is the same value as another cell in another worksheet.

The references require to point to other workbooks and worksheets are difficult, so it is usually best to allow Excel to enter the formulas for you by using the menu or by pointing.

The Process:

Open the files which you wish to link together. When you have more than one workbook file open in Excel you can switch between them by choosing the relevant workbook from the View tab Switch Windows.
Select the data on the source worksheet and then copy the data to the clipboard, using the Home tab Copy.
Switch to the the location where you wish to enter the links and then choose Home tab Paste Paste Link.
The formula should look like this:

='[My Workbook.xls]My Worksheet'!$B$2


Should you want to type-in these formulas then make sure that you use the correct symbols in your expression. Workbook file references are enclosed in square brackets and there is an exclamation mark between the document reference (the workbook and worksheet name) and the local cell reference.

Should either document reference contain a character that would be illegal for a short file name but complies with rules for long file names then the entire document reference has to be enclosed in single quotation marks.

You get an absolute reference (dollar signs) if you copy a single cell but normal, relative references if you copy a block of cells.

When you open a workbook file which contains these linking formulas you will be prompted if you want to update the links. You should normally choose to update the links to ensure that all your data is up to date. If you choose not to update the links


when you open the file then it can be done afterwards by choosing Data tab Edit Links.






For Further Course Details Visit Our Homepage












Microsoft Accredited IT Training Provider
Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators
Microsoft MOS Test Centre London

No comments:

Post a Comment