Documenting and Linking Workbooks and Worksheets

Documenting the Workbook
The textbook often talks about designing a good workbook. One of the good elements is the idea of isolating the assumptions,
another is the idea of a Documentation Worksheet

Each workbook contains up to 255 worksheets, since workbooks are often shared by a group of people in a
corporation it is often a very good idea to have a page of the worksheet that is just for documenting the workbook.

This documentation worksheet shows
 

  1.      the author,
  2.      date of last modification the worksheet
  3.      a description of the overall worksheet
  4.      A list of all sheets in the workbook
  5.      The purpose and contents of each sheet in the workbook
  6.      underlying structure of the worksheet


All of these things seem obvious to you but will be appreciated by the person that follows you in the organization or by you
yourself after having to look at the workbook after not seeing it for several months or years.
Example:

  1. Maximize the Corporate Totals Summary worksheet
  2. Insert new sheet as first sheet and rename it Documentation
  3. Type the following information and format it as follows: Spell check

    4. This is what the documentation worksheet for our Corporate Sales might look like.


Linking External Workbooks

    Instead of consolidating worksheets within one workbook, it might be necessary to leave data in separate workbooks (source workbooks) and then link these to a summary workbook (dependent workbook). This is done by using (external references) in formulas.
The syntax for referencing a cell in another workbook is as follows

     =[Atlanta.xls]Sheet1!B3
where

     Atlanta.xls is the name of the file containing the workbook. The square brackets[] are required to be there to tell
     Excel that this is the filename of an external workbook.
     Sheet 1 is the name of the Worksheet within the file Atlanta.xls. The exclamation point (!) is required to separate the
     name of the worksheet from the name of the file.
     B3 is the name of the cell within the worksheet Sheet1

Hands on Exercise:

  1.     Create a new workbook called Corporate Links
  2.     Open separate workbooks: Atlanta, Boston, and Chicago and arrange these in one screen as before
  3.     Copy and paste column and row titles
  4.     In cell B3 of the corporate links workbook, type = --> point to B3 in the Atlanta sheet --> remove absolute    references --> type + -->
  5.     then point to the same cells in the next two workbooks and repeat step 4 until done.

    6.     The resulting formula in B3   =[Atlanta.xls]Sheet1!B3+[Boston.xls]Sheet1!B3+[Chicago.xls]Sheet1!B3
  6.     Copy the formula to all of the other cells using the fill handle. Note that the workbook and sheet references remain constant whereas the cell references are relative and change with the cells they are copied to.

Here is the table  with the workbook reference filled in.
type of reference Cell Reference Cell Reference including Worksheet Reference
Relative Cell Reference B3 Atlanta!B3
Absolute Cell Reference $B$3 Atlanta!$B$3
Relative Range Reference B3:F3 Atlanta!B3:F3
Absolute Range Reference $B$3:$F3 Atlanta!$B$3:$F$3
Same Relative cell in a range of worksheets Atlanta:Chicago!B3
Same Relative cell Range in a range of worksheets Atlanta:Chicago!B3:F3
Same Absolute cell in a range of worksheets Atlanta:Chicago!$B$3
Same Absolute cell Range in a range of worksheets Atlanta:Chicago!$B$3:$F$3
Syntax of referencing a cell in another Workbook [filename.xls]sheetname!B3
Syntax of referencing a cell range in another Workbook [filename.xls]sheetname!B3:F3

You can create workbook reference as you need them in one of two ways:

  1. Type it in by hand
  2. The mouse method
    1. type the equal sign
    2. double click on the cell you want in the other worksheet.

      3. By default this will give you an absolute cell reference, you can use the F4 key to cycle through the different types of cell references until you get the one you would like.

The F4 Key

The first time you double click on a cell in another workbook it will give you an absolute reference. like this: 
=[Atlanta.xls]Sheet1!$C$4
When you hit F4 it takes you to the mixed reference(relative column, row fixed) . 
=[Atlanta.xls]Sheet1!C$4
Hit F4 again and it takes you to the mixed reference(fixed column, relative row ). 
=[Atlanta.xls]Sheet1!$C4
Hit F4 again an it will take you to the Relative reference 
=[Atlanta.xls]Sheet1!C4

Workbook open verse workbook not open

When both the source an the dependent workbooks are open in the one Excel application window the cell reference is just the filename such as: 
=[Atlanta.xls]Sheet1!C4
When you close the source workbook window (atlanta.xls) the format of the workbook cell reference will change to include the full drive and path of the filename. In this case: 
='H:\Exploring Excel 97\[Atlanta.xls]Sheet1'!C4

Reopening a dependent workbook

When you reopen a dependent workbook, Excel recognizes the fact that there are dependencies and will ask if you want use the old data or get the new information from the workbooks.

If you click yes it will refresh all of the links (without opening workbooks in your application window).
 

Assignments:

  1.     Know terms and multiple choice answers on Pp. 271 - 273
  2.     Hands on Exercises 3 & 4 in Chapter 6
  3.     Be able to do: # 1 & 2 on Pp. 273-275
  4.     Kidstuff on P.278
130 Home        X Home  IS Department at X        Home
by Ruth MacKinnon