On different worksheets

One advantage of a linked formula in our workbook is that the linked cell displays the most recent information.

You had already seen an example of this in lesson 29 for 3D range names.
In this lesson I will discuss these more.
In the example below I have three tabs with the different regions.
In fourth tab I want the totals of the three regions.
I first select the cell with the formula I desire, and begin my formula as usual with the equal sign (=).
I select the tab with the first region (Antwerpen), and select the cell with the total.
I type the plus sign, and select the tab with the second region (Oost Vlaanderen), and select the cell with the total for the region of Oost Vlaanderen.
Type another plus sign, select the last tab (Limburg), select the total for this region, and click the Enter key on the keyboard.

Our formula shows the equals sign first , followed by the function, the name of the tab, an exclamation mark, the cell and the plus sign.

A faster way :
You start as above, but after the equals sign type sum, open parenthesis (
Select the first tab (Antwerpen) and the cell with the total, hold the Ctrl key, and select the last worksheet (Limburg), and close the parentheses.
Click the Enter key on your keyboard.

The second method has two conditions: the tabs must be next to each other and the cells with the totals in the regions should have the same cell address.

Over different workbooks

If you want to link a formula to several workbooks follow the same actions above.
Please note that all of your workbooks must be open.
First select the cell where you want to place the formula.
Type the equals sign and select the cell with the total in the workbook Limburg.
Type a plus sign and select the cell in the second workbook, OVlaanderen, type a plus sign again, and select the cell in the third workbook, Antwerpen.
Click the Enter key on your keyboard.

You can see that the workbooks are listed in brackets in the formula and the cell references are absolute.
If you want to copy this formula please note to make them relative as necessary.

Consolidate Data

Another way to get data from different worksheets isby using the “Consolidate” command.
The “Consolidate” command works best when the layout of the various workbooks is the same.
Open all workbooks plus where you want to Consolidate the cells.
Select the cell in the new workbook where you want the Consolidated data.
Click the “Consolidate” button in the “Data” tab in the “Ribbon”.
This opens the “Consolidate” dialog box.
In the upper section of the dialog you can choose from various functions.
Choose a function and click the “collapse button  and select the first cell .

Click the “Expand” button and click the “Add” button.
Repeat this for all the cells you want to “Consolidate”.

 

 

 

 

 
IWe do not have to check the  “Use labels in” option, because the layout is the same in all our workbooks.
And click OK when you’re done.
Check the box for the text “Create links to the source data”. If we do any modification in a workbook, the data is automatically updated in our workbook where we have placed the consolidated data.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats