Lesson 13: Create Links

Create Links

If you want to use the data from existing spreadsheet in another worksheet or workbook, we need to create links between different worksheets or workbooks.
We will work with two workbooks in this example:
For the sake of clarity, the first workbook is called : Link Source

It gives us the 4th quarter sales for our products:
PCs, modems and printers have their own worksheet

The second is called: Link Target

Open both sheets
Place the cursor in cell B4 of the “Link Target” spreadsheet
Type an equal ‘=’ sign
In the ‘Window’ menu , select the ‘Link Source’ workbook
Select the worksheet ‘PC’
In the worksheet ‘PC’ select the grand total (F8)
Press Enter and the link is created

In the formula, you can see the name of the workbook, worksheet and the selected cell:

Second method for Linking:

We start this time from the “Link Source” workbook

Choose from the menu ‘Window’ Unplug the workbook Source
Click on the ‘Modems’ worksheet
Select the cell F8
Click the right mouse button
Select ‘Copy’
Link via the Window menu to connect with the ‘Link Target’ workbook
Select the B5 cell in which the linked data has to be included
Click the right mouse button
In the pop-up menu select the ‘paste special ‘(Paste Special) option
In the dialog box, click the ‘paste link’ (Paste link) command

The linking is performed immediately.
The first method is the fastest.

Why not copy and link

In a linked cell , when we change data in the source file it will automatically reflected in the linked files. Ex:

Select ‘Window’ menu and select all windows “(Arrange) “- “Horizontal arrange” (Horizontal)
Activate the workbook “Link Source”, by clicking on the title bar
Select the worksheet ‘PC’
You will notice that if you change data anywhere in the spreadsheet , the grand total turnover in the column is also changed:

BEFORE:

Any change in any cell of the “Link Source” workbook is automatically updated in the “Link Target” worksheet

AFTER:

Merging data

Data that is spread over several worksheets or sources can easily be merged.

In the following example, there are four worksheets with which we can merge data in two different ways:

1.Merge per position 2.Merge per category

1. Consolidate by position: Here, you can combine data from multiple worksheets wherein the arrangement of the data and the formatting is completely identical. The layout of the worksheets which are to be merged is identical.

We create a worksheet and call it eg “Merge per position”
Select cell B3 in the new worksheet
Select the Data menu, select ‘Consolidate’ (Consolidate)
In the function box, enter the desired function (usually it is SUM)
In the box you must also give reference to the different source areas that you want to enter:
- Click on the sheet ‘Antw. ’95’ And select the area B3 to E6
- Click on “Add” (Add)
- Repeat this action for all source areas
  (Antw. ’96 ‘-‘ Limb. ’95 ‘And’ Limb. ’96 ‘)

Click OK
If you want the merged table to be updated whenever the source data is updated, then activate the ‘Create links to source data’ (Create links to source data) at the bottom.
Ok

All data is merged, all changes in the source file are automatically reflected in the merged file.

Result:

Consolidate by category

This option is used if you want to merge the source file with different categories (eg Antwerp and Limburg do not sell the same products)

Click the first cell of the merging area, ‘cell A3’
Choose the Data menu, select the ‘Merge’ (Consolidate)
The function remains ‘SUM’
Select the ‘Reference’ (Reference) section
Click on the worksheet ‘Antw. ’95’
Select the area. (including the products)
Click add
Also you need to do this action for all source areas
Activate “Use labels in the left column” (Use labels in Left Column)
Also activate “Create Links to source data” (Create links to source data)
Press OK

The merge is performed by category.
You will notice that the outline symbols are automatically displayed.

Awesome!
You've completed Lesson 13 START NEXT LESSON

Lesson 13

Office 97 - Excel