On different “Worksheets”

One advantage of a linked “Formula” is that, when information changes, the linked cell will display the most recent information in our “Workbook”.  We went through an example of this in Lesson 29 with 3D “Range” names.

In this lesson I will expand a little on this.

In the example below I have three tabs with the different regions. I also created a fourth tab in which I wish to display the totals of the three regions. First I select the cell where I want to have the results and begin the “Formula” with the equal sign (=).

I select the tab with the first region (Antwerp), and select the cell with the total. Next, I type the “+” character, select the tab of the second region (East Flanders) and select the cell with the total for the region of East Flanders. Again, I type another “+” sign and select the last tab (Limburg), select the total for this region, and click the Enter key on my keyboard.

Our “Formula” shows an equal sign, followed by the name of the tab, an exclamation point, the cell and a plus sign. This is repeated for all selected tabs.

You can do this a bit faster when you take advantage of the “SUM” function.
You start the same way as above. After entering the equal sign, type sum, open parentheses (Select the first tab (Antwerp) and the cell with the total, hold the Shift key, select the last “Worksheet” (Limburg). And click the Enter key on your keyboard.

This latter method has two conditions. The tabs need to be next to each other and the cells with the regional totals have to have the same cell address within their sheets

 

In different “Workbooks”

To link a “Formula” through several workbooks, follow the same actions as above. Please note that all of your “Workbooks” should be open. First select the cell which will contain the “Formula”. Type the equal sign, and select the cell with the total in the Limburg “Workbook”. Type a plus sign and select the cell in the second “Workbook” which is East Flanders, type a plus sign again, and select the cell in the third “Workbook”, Antwerp. Click the Enter key on your keyboard.

You can see that the “Formula” in the “Workbooks” listed in brackets, are used as absolute cell references. Please note that if you want to copy this “Formula”, you need to make this a relatively cell first.

 

Data “Consolidation”

Another way to get data together from different “Worksheets” is through the “Consolidate” command.

The “Consolidate” command works best when the layouts of the various “Workbooks” are the same.

Open all the “Workbooks” and create another one where you want to consolidate the cells. Select a cell in the new “Workbook” where you want to consolidate the data. Click the “Consolidate” button under the “Data” tab in the “Ribbon”. This opens the “Consolidate” dialog box.

In the upper section of the dialog box you can choose from various functions. Choose a function and click the “Folding” button in the first cell to select it. Select the cell, click the “Open” button and click the “Add” button. Repeat for all the cells you want to merge.

In the “Using labels” section we can select anything because the layout is the same in all our “Workbooks”. Click OK when you’re done.  Check the box for the “Links to the source data” to allow for any modification in a “Workbook” to be accounted for. The changed data automatically integrates into our “Workbook” where we have consolidated the data.

 

 

Featured Courses

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