Format & Arrange:
Link

Step-by-Step: Link

---

What you will learn:

to create a link to use Paste Link to correct a linking formula

---

Start with: trips19.xls - Specials & Tahiti (saved in previous lesson)

To have the data on the sheet Tahiti updated whenever the original on the sheet Specials changes, you must link the cells.

In the Formula bar  =Specials!A4  would link that cell to cell A4 on the Specials sheet. Note the exclamation point ! between the sheet name and the cell reference. You can do this yourself if there are just a few cells. For many cells you can use Paste Link on the Paste Special dialog to do them when you paste.

When you link cells, you do not get the formatting also. You must apply the formatting separately.

---

Link: Keyboard

1. Select cell A1 on the sheet Tahiti.
    
2. Type  =Specials!A1  and click the green check mark. The cell A1 is still selected and looks the same as before, but now, if you change cell A1 on the sheet Specials, this cell will change, too. This is the purpose of linking.

   
    
    

3. Switch to the sheet Specials and edit cell A1 in some way.
    
4. Return to Tahiti to see if its cell A1 changed to match. (It should have!)
    
5. Use Undo to return cell A1 remove your changes. (You do not have change sheets first. There is only one Undo list.)

---

Link by mouse:

1. Select cell A2 on sheet Tahiti. Clear the contents. ( Edit | Clear | All ). The cell is now blank and is no longer merged.
    
2. Type an equals sign  =
    
3. Switch to sheet Specials and click on cell A2.
    
4. Press ENTER. You are switched back to the sheet Tahiti. The text Anniversary Specials shows up, but there is no formatting.
    
5. Select cell A2 again. The Formula bar shows  = Specials!A2  for the contents of cell A2.

This mouse method of linking cells avoids the problem of fumble fingers not typing the name of the sheet or the cell reference correctly. It would get tiresome to do this for every cell in the table, however. Redoing the formatting would certainly get old in a hurry. Next we will look at a method for linking cells and getting the same formatting. It's awkward, but it works.

If you forget to press ENTER after selecting the cell to link, your next clicks also put cell references in the Formula bar. Quite a mess. The Undo command is a real help when this happens.

---

Link: Paste Link

Using  Paste Special  |  Paste Link  to link a number of cells will not copy the formatting of the original cells. But a second use of Paste Special with Formats selected will copy just the formats.

1. Clear table:  On the sheet Tahiti, select the whole table, A1:F11, and clear it with  Edit  |  Clear  |  All . You are back to a blank sheet.
    
2. Switch to sheet Specials and select ranges A1:F10 and A25:F25 (the data about Tahiti trips) again and Copy.
    
3. Switch to sheet Tahiti and select cell A1.
    
4. Select from the menu  Edit  |  Paste Special... and click the button Paste Link. The non-adjacent data you copied is pasted on the sheet Tahiti as adjacent cells.
    
5. Click on several cells in the paste area and check what the Formula bar shows.

   All the cells in the table on Tahiti are linked to the cells in Specials.

   Three Problems:
   

   1. No formatting was copied with the linking.
       
   2. A zero is shown when the original cell was blank.
       
   3. Totals are incorrect for this table.
      In Excel 97 - Row 11 is supposed to be linked to cells in Row 25 on the sheet Specials. Instead Row 11 cells are linked to Row 35! This is a serious glitch, caused by linking ranges that are not adjacent to each other in the original sheet. Excel is really confused! Therefore:

        Don't try to Paste Link non-adjacent ranges in Excel 97. Copy each range separately.

      In the next step you will fix Problem a. You will fix problems b and c by editing the cell references by hand later.
       

6. Fix Problem a- Lost Formatting: 
   Select A1 on the Tahiti sheet and from the menu select  Edit  |  Paste Special  and choose Formats in the dialog. Click OK. AutoFit column F to show the entire total.

   If the Paste Special... command is grayed out or you get the wrong dialog, the Clipboard has lost your copied data. You have done something since you pasted last. Return to sheet Specials, copy the cells (they should still be selected), and try again.

   So, oddly enough, for non-adjacent ranges you can Paste Special the formats accurately, but you can't Paste Link the cells accurately. Just remember, if you forget this difference later, it's not you that's crazy. It's Excel!
    

7. Fix Problem b - Zeros in blank cells:  Delete the zeros from all Tahiti sheet cells that should be blank- all cells in row 3, A11, B11. Delete the contents of cell E11.

---

Repair a Formula

Something odd is going on with the totals cells. These totals cells show the sums for the whole column on the sheet Specials. This is not what you really want. You want the sum of the values that are on this sheet. So these totals  cells should not be linked after all.

1. Select cell D11 and click on the AutoSum button and press ENTER.
    
2. Repeat for cell F11.
    
3. If necessary, resize Row 4 to just fit the height of the labels.
    
4. Save as  trips20.xls  on your Class disk in the excel project 3 folder.
   How to handle a full disk

---

Test the links

1. Test the links by changing some values for the Tahiti trips on Specials, and then looking at the Tahiti sheet to see if the changes are shown there. Change the names as well as the number of people and the price per ticket.
    
2. Undo all your experiments! (Remember- You saved before you started your experiments. You can close the file without saving changes and open it up again to get back to where you were.)

---

Sheet Header

1. With sheet Tahiti active, open Print Preview. There is no header! Sadly, you must do a header separately for each sheet in a workbook.
    
2. Click on the Setup… button and then the Header/Footer tab.
    
3. Create a custom header for the Tahiti sheet - Left section has your name and the date; Center section has the file name and sheet name; Right section has Excel Project 3. Click OK to close the dialog.
    
4. Make corrections to the sheet if necessary. Close Print Preview.
    
5. Save. [trips20.xls]